The SQL UPDATE statement modifies the data of one or more records in a table.  It has the following syntax:

UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition];

Parameters:

  • UPDATE table_name  – The name of the table to update.
  • SET column_name  –  Indicates which columns to modify and the values they should be given.
  • WHERE condition – Optional. Specifies which records should be updated. If you omit it, all records in the table will be updated. You can also combine N number of conditions using the AND or the OR operators.

Example:

Consider the Products table having the following records:

id product quantity price
1 Glass Table 10 899.99
2 Oak Desk 28 530.50
3 Gloss Coffee Table 34 240.10
4 Oak Table 10 450.90
5 Birch Chair 8 60.80
UPDATE Products SET quantity = 9  WHERE product = ‘Oak Table’;

Will produce the following table:

id product quantity price
1 Glass Table 10 899.99
2 Oak Desk 28 530.50
3 Gloss Coffee Table 34 240.10
4 Oak Table 9 450.90
5 Birch Chair 8 60.80

If you omit the WHERE clause, the following query will modify all the ‘quantity’ column values:

UPDATE Products SET quantity = 9;
id product quantity price
1 Glass Table 9 899.99
2 Oak Desk 9 530.50
3 Gloss Coffee Table 9 240.10
4 Oak Table 9 450.90
5 Birch Chair 9 60.80

Notes:

  • The user must have data manipulation privileges on the table or column (UPDATE privilege).
  • The updated value must not conflict with all the applicable constraints (primary keys, unique indexes, CHECK constraints, and NOT NULL constraints).

Was this post helpful?