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:

• UPDATE
• INSERT
• DELETE
• SELECT

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.