The UPDATE statement allows you to update existing rows with new values for one or more columns.
An UPDATE will modify every row of the table unless a WHERE clause is provided.
It is not required to provide a WHERE clause but it is highly recommended to avoid accidental updates to the entire table. Some database engines will warn or even prevent you from running a table-wide UPDATE statement.
You can run a SELECT statement with the same WHERE clause to check which rows will be altered with an UPDATE before actually performing the update.
Remember that each example on this website is editable and runnable so you can see the results of your UPDATE by querying the contents of the table, i.e.: SELECT * FROM users;
UPDATE users
SET email_address = 'james@mitchellfamily.co'
WHERE id = 1;
1 row affected.
You can update multiple columns within the same UPDATE statement.
UPDATE users
SET active = 0,
email_address = NULL
WHERE id = 1;
1 row affected.
An UPDATE can use the result of a computation. This example shows using negation logic to toggle the value of a column between 0 and 1 on each update.
UPDATE users
SET active = NOT active
WHERE id = 1;
1 row affected.
Similar to an INSERT, UPDATE accepts an optional fallback strategy using OR when the update would cause a constraint violation and fail.
Try removing the OR IGNORE clause and run the example again. Notice that the UPDATE now fails with an error.
UPDATE OR IGNORE users
SET id = 1,
name_first = 'James',
name_last = 'Mitchell',
email_address = 'jmitchell@email.net',
created_at = datetime('now'),
active = 1
WHERE id = 2;
0 rows affected.
Some database engines support the RETURNING clause, which will cause the database to return the rows that were updated. You can specify column names to return or * to include all columns.
UPDATE users
SET email_address = 'james@mitchellfamily.co'
WHERE id = 1
RETURNING id, email_address;