What is UPDATE command for SQL [Understand with 5 Examples]

What is SQL update command?

The UPDATE command for SQL is used to modify the existing data in a SQL table. UPDATE statement always used with WHERE clause as condition and based on condition query make the modification in the SQL table

let’s go through below syntax to understand more

Syntax:

UPDATE 
      table_name
SET 
      col_Name1 = 'value1', col_Name1 = 'value1', ... col_Namen = 'valuen'
[WHERE condition]

table_name is the name of table in which you would like to do the modification
SET is the syntax which used with UPDATE to set the values of columns which you like to modify or update.
WHERE is the clause which used to put condition on which row modification can be done in table.

Lets look at some frequent ask questions:

What are the three UPDATE command in SQL?

There are three basic update command in SQL which you can use to manipulate SQL table. you can create, modify and delete.

INSERT – This command you can use to INSERT records as single or multiple in the SQL table.
UPDATE – This is main UPDATE command use to modifies the existing table record
DELETE – This command use to removes the SQL record from the database.

How to UPDATE 2 records in SQL?

You can use where clause to UPDATE 2 records in SQL with the help of “IN” keyword as shown below image

UPDATE orders_Tbl
SET Customer_ID = 'XYZ', 
    [Order Priority] = 'Low'
WHERE [Customer ID] IN ('7102', '4356');

Is UPDATE command is DML category?

Yes, Update command in SQL is the DML command Data Manipulation Language and others example, Insert, Delete, Modify etc.

Below are 5 examples which will make you to understand better with different scenario.

Example #1: Update one column

Let’s check the current status for Customers_ID = ‘7102’ in Orders_Tbl table

SELECT
      * 
FROM
      Orders_Tbl
WHERE
      [Customer ID] = '7102'

Result:

In above result you can see that “order priority” has different priority.

Now lets run Update command for SQL to put “order priority” as ‘Very Low’ for [Customer ID] = ‘7102’

UPDATE 
      Orders_Tbl
SET 
      [Order Priority] = 'Very Low'
WHERE
      [Customer ID] = '7102'

Result: its updated the 17 rows with [Order Priority] = ‘Very Low’

Let’s verify after UPDATE command

SELECT
      * 
FROM
      Orders_Tbl
WHERE
      Customer ID] = '7102'

Result: Now order priority has been changed to “Very Low”

Lets see below few more examples

Example #2: Update more then one columns

In the below example, query do the modification in 3 columns Product_Category, Region and Customer_ID based on one condition that is Customer_Name = ‘Jack Lebron’

UPDATE 
      [Customers_Tbl] 
SET 
      Product_Category = 'New Furniture' ,
      Region = 'West North' ,
      Customer_ID = '1111' 
WHERE 
      Customer_Name = 'Jack Lebron'

Example #3: Update columns using multiple conditions

In below example, query do the modification for 2 columns based on multiple 2 condition on two different columns on Customer_Name and Product_Container

In this UPDATE command uses the Logical Operator AND , it means if both condition true then it will do the modification in table.

UPDATE
      [Customers_Tbl]
SET
      Customer_Segment = 'New office' ,
      Product_Category = 'AI Technology'
WHERE
      Customer_Name = 'Jim Sink' 
      AND 
      Product_Container = 'Small Box'

Example #4: Update columns for NULL values

In the below example, we are using two WHERE conditions using logical Operator OR, this will do the modification if any one of column is NULL value or both have NULL values

UPDATE
      [Customers_Tbl]
SET
      Province = 'Quebec' ,
      Product_Category = 'AI Technology' 
WHERE
      Province is NULL 
      OR 
      Product_Category is NULL

Example #5: Update command using CASE statement

In below example UPDATE command using CASE statement to update Customer_Segment column based on CASE statement multiple conditions on Region column.

UPDATE
    [Customers_Tbl]
SET Customer_Segment = 
                     CASE
                     WHEN Region='West' Then 'West Segment'
                     WHEN Region='Atlantic' Then 'Atlantic Segment'
                     WHEN Region='Ontario' Then 'Ontario Segment'
                     Else Region
                     END)

With CASE statement, update command changing the name of different region in whole SQL table. It doesn’t matter how much size of table in SQL. it will change all region name with new name based on condition.

Conclusion:

Update command for SQL is powerful way to make changes, you can use these 5 example and do practice with SQL table, so that you can remembered how to use update command syntax.