SQL Query to Update All Rows in a Table
The Update statement is a SQL keyword to update data in the database. We can update all the rows in the database or some values with the help of conditions. The update is a SQL keyword and it falls under Data Manipulation Language(DML), as the name suggests it is used to manipulate the data. There are many DML keywords that fall under this:
SQL is a case insensitive language i.e SELECT * FROM PRODUCTS is the same as select * from products. But we write keywords in capital letters and tables, column names in small letters as a convention.
Step 1: CREATE DATABASE
Syntax:
CREATE DATABASE database_name;
Step 2: TO CREATE TABLE(PRODUCTS)
Query:
CREATE TABLE products(product_id int primary key, product_name varchar(45), product_cost float);
Step 3: TO INSERT VALUES IN TABLE
Query:
INSERT INTO products VALUES (1001,'Colgate Toothpaste', 2.25), (1002, 'Sensodyne Toothpaste', 2.30), (1003, 'Listrine Mouthwash', 1.75), (1004, 'T-Shirt', 1.75), (1005, 'Pants', 2.35);
Step 4: Now let’s see the data inside the table we created.
Query:
SELECT * FROM products;
Output:
Step 5: TO UPDATE ALL DATA
Syntax:
UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- ;
Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value. Let’s look at an example.
Now our task is to update the product_cost to 4, for all product_id(s), so let’s see how it is done.
Query:
UPDATE products SET product_cost = 4;
Output:
As you can see product_cost for all product_id(s) is changed to 4.
Syntax:
UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- WHERE condition;
Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value, WHERE is used to filter for specific data.
Let’s look at an example.
Now our task is to update the product_cost to 4, for product_id 1001, so let’s see how it is done.
Query:
UPDATE products SET product_cost = 4 WHERE product_id = 1001;
Output:
As you can see product_cost for product_id 1001 is changed to 4.