sql_update

SQL UPDATE Statement

The UPDATE statement in SQL is used to modify existing records (rows) in a table. It allows you to change the values in one or more columns for rows that match a specified condition.

The SQL UPDATE Statement Explained

The UPDATE statement targets specific rows using a WHERE clause and changes the values of specified columns for those rows.

SQL UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

table_name: The name of the table you want to update.SET column1 = value1, column2 = value2, ...: A comma-separated list of columns to update and their new values. Values can be literals, expressions, or subquery results (depending on RDBMS).WHERE condition: An optional clause that specifies which rows to update. If omitted, all rows in the table will be updated.

Demo Database

Let’s use a conceptual “Customers” table for our examples.

Initial “Customers” table data:

CustomerID | CustomerName                        | ContactName     | Address                        | City        | PostalCode | Country
---------- | ----------------------------------- | --------------- | ------------------------------ | ----------- | ---------- | -------
1          | Alfreds Futterkiste               | Maria Anders    | Obere Str. 57                  | Berlin      | 12209      | Germany
2          | Ana Trujillo Emparedados y helados  | Ana Trujillo    | Avda. de la Constitución 2222  | México D.F. | 05021      | Mexico
3          | Antonio Moreno Taquería             | Antonio Moreno  | Mataderos 2312                 | México D.F. | 05023      | Mexico
4          | Around the Horn                     | Thomas Hardy    | 120 Hanover Sq.                | London      | WA1 1DP    | UK

SQL UPDATE Example

Suppose we want to update the contact person and city for the customer “Alfreds Futterkiste”. It’s best to use a unique identifier like CustomerID in the WHERE clause.

We use the following SQL statement (assuming CustomerID for ‘Alfreds Futterkiste’ is 1):

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Hamburg'
WHERE CustomerID = 1;

If CustomerID is unknown, you might use another condition, but be cautious if it’s not unique:

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Hamburg'
WHERE CustomerName = 'Alfreds Futterkiste';

After this update (targeting CustomerID = 1), the selection from the “Customers” table will look like this:

CustomerID | CustomerName                        | ContactName     | Address                        | City        | PostalCode | Country
---------- | ----------------------------------- | --------------- | ------------------------------ | ----------- | ---------- | -------
1          | Alfreds Futterkiste               | Alfred Schmidt  | Obere Str. 57                  | Hamburg     | 12209      | Germany
2          | Ana Trujillo Emparedados y helados  | Ana Trujillo    | Avda. de la Constitución 2222  | México D.F. | 05021      | Mexico
3          | Antonio Moreno Taquería             | Antonio Moreno  | Mataderos 2312                 | México D.F. | 05023      | Mexico
4          | Around the Horn                     | Thomas Hardy    | 120 Hanover Sq.                | London      | WA1 1DP    | UK

Update Multiple Columns

You can update multiple columns in a single UPDATE statement by separating the assignments with commas in the SET clause, as shown in the example above.

Example: Increase the price of all products in a certain category by 10% and mark them as ‘Special Offer’. (Conceptual ‘Products’ table)

UPDATE Products
SET Price = Price * 1.10, Status = 'Special Offer'
WHERE CategoryID = 5;

Update Warning! The Danger of Omitting WHERE

Be extremely careful when updating records. If you omit the WHERE clause, all records in the table will be updated. For example, if we executed:

UPDATE Customers
SET ContactName = 'Global Contact', City = 'Default City';

Then, the “Customers” table would have ALL rows changed:

CustomerID | CustomerName                        | ContactName    | Address                        | City          | PostalCode | Country
---------- | ----------------------------------- | ---------------- | ------------------------------ | ------------- | ---------- | -------
1          | Alfreds Futterkiste               | Global Contact   | Obere Str. 57                  | Default City  | 12209      | Germany
2          | Ana Trujillo Emparedados y helados  | Global Contact   | Avda. de la Constitución 2222  | Default City  | 05021      | Mexico
3          | Antonio Moreno Taquería             | Global Contact   | Mataderos 2312                 | Default City  | 05023      | Mexico
4          | Around the Horn                     | Global Contact   | 120 Hanover Sq.                | Default City  | WA1 1DP    | UK

This is rarely the intended behavior and can lead to significant data loss or corruption.

Best Practices for UPDATE:

• Always use a WHERE clause unless you specifically intend to update all rows.
• Prefer using primary keys or unique identifiers in your WHERE clause for precision.
• Test with SELECT first: Before running an UPDATE, write a SELECT statement with the same WHERE clause to verify which rows will be affected.
• Backup your data: Especially before running large-scale or complex UPDATE statements in a production environment.
• Use Transactions: For multiple related updates, or if you need the ability to rollback, perform UPDATE statements within a transaction (BEGIN TRANSACTION, COMMIT, ROLLBACK).

Some RDBMS also support updating rows in one table based on values from another table, often using joins or subqueries within the UPDATE statement. The syntax for this is more advanced and RDBMS-specific.