The SQL UPDATE
statement is used to edit an existing row in a database table.
-- update a single value in the given row
UPDATE Customers
SET age = 21
WHERE customer_id = 1;
Here, the SQL command changes the value of the age column to 21 if customer_id is equal to 1.
The syntax of the SQL UPDATE
statement is:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];
Here,
table_name
is the name of the table to be editedcolumn1, column2, ...
are the names of the columns to be editedvalue1, value2, ...
are values to be set to the respective columns[...]
signifies that the clause inside is optionalcondition
is the condition for the values to be changedIn SQL, a single value can be updated by using the UPDATE
command with the WHERE
clause. For example,
-- update a single value in the given row
UPDATE Customers
SET first_name = 'Johnny'
WHERE customer_id = 1;
Here, the SQL command changes the value of the first_name column to Johnny if customer_id is equal to 1.
Example: SQL UPDATE Statement
Note: If we want to insert a new row instead of updating an existing row, we can use the SQL INSERT INTO statement.
We can also update multiple values in a single row at once. For example,
-- update multiple values in the given row
UPDATE Customers
SET first_name = 'Johnny', last_name = 'Depp'
WHERE customer_id = 1;
Here, the SQL command changes the value of the first_name column to Johnny and last_name to Depp if customer_id is equal to 1.
The UPDATE
statement can update multiple rows at once. For example,
-- update multiple rows satisfying the condition
UPDATE Customers
SET country = 'NP'
WHERE age = 22;
Here, the SQL command changes the value of the country
column to NP if age is 22. If there are more than one rows where age equals to 22, all the matching rows will be edited.
We can update all the rows in a table at once by omitting the WHERE
clause. For example,
-- update all rows
UPDATE Customers
SET country = 'NP';
Here, the SQL command changes the value of the country column to NP for all rows.
Note: We should be cautious while using the UPDATE
statement. If we omit the WHERE
clause, all the rows will be changed and this change is irreversible.