SQL update statement

Last update on October 03 2024 13:14:35 (UTC/GMT +8 hours)

Update statement

Once there is some data in the table, it may be required to modify the data. To do so, the SQL UPDATE command can be used. It changes the records in tables.

The SQL UPDATE command changes the data which already exists in the table. Usually, it is needed to make a conditional UPDATE in order to specify which row(s) are going to be updated.

The WHERE clause is used to make the update restricted and the updating can happen only on the specified rows.

Without using any WHERE clause (or without making any restriction) the SQL UPDATE command can change all the records for the specific columns of a table.

Syntax:

UPDATE < table name > SET<column1>=<value1>,<column2>=<value2>,.....
WHERE <condition>;

Parameters:

Name	Description
table_name	Name of the table to be updated.
column1,column2	Name of the columns of the table.
value1,value2	New values.
condition	Condition(s) using various functions and operators.

Syntax diagram - UPDATE STATEMENT

Some important questions regarding the SQL UPDATE command

What is the SQL UPDATE command used for?

The SQL UPDATE command is used to modify existing records in a table in a database.

What does the WHERE clause do in an UPDATE statement?

The WHERE clause filters the rows in the table, specifying which records should be updated. If omitted, all rows in the table will be updated.

How can you update multiple columns using the UPDATE command?

You can update multiple columns by listing each column and its new value separated by commas in the SET clause.

What happens if you omit the WHERE clause in an UPDATE statement?

If the WHERE clause is omitted, all rows in the table will be updated, resulting in potential unintended consequences or data corruption.

How can you update data in one table based on values from another table?

You can use a JOIN operation in the UPDATE statement to update data in one table based on values from another table.

How can you verify that the update operation was successful?

After executing the UPDATE statement, you can check the number of rows affected to verify that the desired records were updated. Most database systems return the number of rows affected by the update operation.

SQL update for a specific column

Data for a specific column(s) can be changed with the SQL UPDATE statement.

Example:

Sample table: neworders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

To change the value of 'ord_description' of 'neworder' table with 'ZOD', the following SQL statement can be used :

SQL Code:


-- This SQL code updates the 'ord_description' column in the 'neworder' table.
-- UPDATE statement begins
UPDATE neworder
-- Specifies the target table 'neworder' where the data will be updated
SET ord_description='ZOD';
-- Sets the value of the 'ord_description' column to 'ZOD' for all rows in the 'neworder' table

Explanation:

This SQL code uses the UPDATE statement to modify existing records in the 'neworder' table.

The UPDATE statement specifies the target table 'neworder' where the update operation will be performed.

The SET clause assigns the new value 'ZOD' to the 'ord_description' column for all rows in the 'neworder' table.

Since no WHERE clause is provided, the update operation will affect all rows in the table, setting the 'ord_description' column to 'ZOD' for each row.

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Insert using nested subqueries with any operator
Next: Update with condition