By Matthew Rathbone · November 21, 2026

The SQL UPDATE statement changes data in rows that already exist. You name the table, list the columns to change with SET, and pick the rows with WHERE:

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

Every row that matches the condition gets the new values. Leave out WHERE and every row in the table is updated. UPDATE never adds or removes rows: adding is INSERT, removing is DELETE.

Jump to: sample data · update one row · multiple columns · multiple rows · calculate from the current value · preview first · update all rows · different values with CASE · NULL · undo with ROLLBACK · see what changed · UPDATE with a subquery · update from another table · UPDATE with JOIN · upsert · batches · 0 rows updated · constraint errors · ON UPDATE · database differences · performance · mistakes · FAQ

Sample data

Every query below was run on PostgreSQL 16 and SQLite 3.45, and the output is copied from the real result (PostgreSQL's formatting is shown). Each example starts from this fresh data, so the updates don't pile up on each other. Where the two databases differ, it's called out. MySQL, SQL Server and Oracle syntax comes from their documentation.

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY, name TEXT, country TEXT
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT, category TEXT,
  price NUMERIC(8,2), stock INTEGER,
  supplier_id INTEGER REFERENCES suppliers(id),
  discount NUMERIC(8,2)
);
CREATE TABLE new_prices (
  product_id INTEGER, price NUMERIC(8,2)
);
INSERT INTO suppliers VALUES
 (1, 'Acme',     'US'),
 (2, 'Borealis', 'CA'),
 (3, 'Castell',  'ES');
INSERT INTO products VALUES
 (1, 'Desk lamp',     'lighting',    24.00,  40, 1,    NULL),
 (2, 'Floor lamp',    'lighting',    89.00,   0, 1,    10.00),
 (3, 'Office chair',  'furniture',  150.00,  12, 2,    NULL),
 (4, 'Standing desk', 'furniture',  420.00,   5, 2,    20.00),
 (5, 'Notebook',      'stationery',   4.50, 300, 3,    NULL),
 (6, 'Gel pen',       'stationery',   1.20,   0, NULL, NULL);
INSERT INTO new_prices VALUES
 (1, 26.00), (4, 399.00), (5, 4.75);

Things to notice for later: only two products have a discount (the rest are NULL), the gel pen has no supplier, and new_prices is a small "price list" table with new prices for three of the six products.

Update one row

To change a single row, filter on something unique, almost always the primary key:

UPDATE products
SET price = 27.50
WHERE id = 1;
UPDATE 1

PostgreSQL reports how many rows it changed (UPDATE 1); MySQL says Rows matched: 1 Changed: 1, SQL Server (1 row affected), and in SQLite you can check with SELECT changes();. Only the desk lamp has a new price:

SELECT id, name, price FROM products ORDER BY id;
 id |     name      | price
----+---------------+--------
  1 | Desk lamp     |  27.50
  2 | Floor lamp    |  89.00
  3 | Office chair  | 150.00
  4 | Standing desk | 420.00
  5 | Notebook      |   4.50
  6 | Gel pen       |   1.20
(6 rows)

Filtering on a name (WHERE name = 'Desk lamp') works too, but it changes every row with that name. If you mean one specific row, use its id.

Update multiple columns

Separate the assignments with commas. There is one SET keyword, however many columns you change:

UPDATE products
SET price = 79.00,
    stock = 25,
    discount = NULL
WHERE id = 2;
UPDATE 1

 id |    name    | price | stock | discount
----+------------+-------+-------+----------
  2 | Floor lamp | 79.00 |    25 |
(1 row)

Two common syntax errors here: writing SET twice (SET price = 79, SET stock = 25) and joining the assignments with AND. The second is nasty because it sometimes runs: in MySQL, SET price = 79 AND stock = 25 is read as price = (79 AND stock = 25), a true/false expression, so price quietly becomes 0 or 1.

PostgreSQL, SQLite and Oracle also accept a column list on the left, which reads nicely when the values come from a subquery:

UPDATE products
SET (price, stock) = (99.00, 7)
WHERE id = 2;

MySQL and SQL Server don't support that form. There's more on this in how to update multiple columns in SQL.

Update multiple rows

UPDATE takes the same WHERE clause as SELECT, so anything you can filter, you can update. A condition that matches several rows changes all of them in one statement.

By a column value

UPDATE products
SET stock = 50
WHERE category = 'stationery';
UPDATE 2

A list of ids with IN

UPDATE products
SET supplier_id = 3
WHERE id IN (1, 2);
UPDATE 2

Several conditions

UPDATE products
SET discount = 5.00
WHERE category = 'furniture'
  AND price > 200;
UPDATE 1

Only the standing desk is furniture over 200. Combine conditions with AND and OR exactly as in a query, and use parentheses whenever you mix them: AND binds tighter than OR, so WHERE category = 'lighting' OR category = 'furniture' AND price > 200 updates all the lighting. LIKE patterns and BETWEEN ranges work here too.

Update using the current value

The right-hand side of SET can be any expression, including the column's own current value. This is how you raise prices, decrement stock or tidy up text.

Increase by a percentage

UPDATE products
SET price = price * 1.10
WHERE category = 'lighting';
UPDATE 2

 id |    name    | price
----+------------+-------
  1 | Desk lamp  | 26.40
  2 | Floor lamp | 97.90
(2 rows)

PostgreSQL's NUMERIC(8,2) column keeps exactly two decimals. SQLite has no true decimal type, so the same statement stores the desk lamp as 26.400000000000002. If your column is a float, or you're on SQLite, round as you go: SET price = ROUND(price * 1.10, 2).

Add or subtract

UPDATE products
SET stock = stock - 1
WHERE id = 3;

The chair's stock goes from 12 to 11. Doing the arithmetic inside the UPDATE (rather than reading the value into your app, subtracting, and writing it back) matters when two people buy at once: the database applies each decrement to the latest value, so neither sale is lost.

Text functions

UPDATE products
SET name = UPPER(name),
    category = REPLACE(category, 'stationery', 'office supplies')
WHERE id = 5;
 id |   name   |    category
----+----------+-----------------
  5 | NOTEBOOK | office supplies
(1 row)

Any function works on the right-hand side; REPLACE in an UPDATE is the usual way to fix a typo or a changed domain across a whole column.

Which value does SET see: old or new?

In standard SQL, every expression on the right reads the row as it was before the update. That makes swapping two columns a one-liner:

UPDATE products
SET price = stock,
    stock = price
WHERE id = 3;
 id | price | stock
----+-------+-------
  3 | 12.00 |   150
(1 row)

PostgreSQL, SQLite, SQL Server and Oracle all behave this way. MySQL is the exception: it evaluates the assignments left to right, so by the time it reaches stock = price, price already holds the new value and both columns end up as 12. In MySQL, order your assignments with that in mind.

Always preview with SELECT first

The single best habit with UPDATE: write it as a SELECT first, with the new value as an extra column, and look at the rows.

-- 1. Look
SELECT id, name, price,
       ROUND(price * 1.10, 2) AS new_price
FROM products
WHERE category = 'lighting';
 id |    name    | price | new_price
----+------------+-------+-----------
  1 | Desk lamp  | 24.00 |     26.40
  2 | Floor lamp | 89.00 |     97.90
(2 rows)
-- 2. Update the same rows
UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE category = 'lighting';
UPDATE 2

The row count from the update should match the preview. If the preview says 2 rows and the update says 2,000, stop, and that's what a transaction is for.

Update all rows in a table

An UPDATE without WHERE changes every row:

UPDATE products
SET stock = 0;

SELECT COUNT(*) AS products, SUM(stock) AS total_stock FROM products;
UPDATE 6

 products | total_stock
----------+-------------
        6 |           0
(1 row)

Sometimes that's what you want (resetting a counter, filling a new column). Much more often it's an accident: the WHERE was on the next line and only the first lines were selected in the SQL editor when someone pressed run. Unlike a delete, nothing looks missing afterwards; the table has the same number of rows, all with the same wrong value.

Update rows with different values (CASE)

To give different rows different values in one statement, put a CASE expression on the right-hand side:

UPDATE products
SET price = CASE category
              WHEN 'lighting'  THEN price * 0.90
              WHEN 'furniture' THEN price - 20
              ELSE price
            END;
UPDATE 6

 id |     name      |  category  | price
----+---------------+------------+--------
  1 | Desk lamp     | lighting   |  21.60
  2 | Floor lamp    | lighting   |  80.10
  3 | Office chair  | furniture  | 130.00
  4 | Standing desk | furniture  | 400.00
  5 | Notebook      | stationery |   4.50
  6 | Gel pen       | stationery |   1.20
(6 rows)

Don't forget ELSE price. A CASE with no matching branch returns NULL, so without the ELSE the same statement wipes the price of everything that isn't lighting or furniture:

  5 | Notebook      | stationery |
  6 | Gel pen       | stationery |

Better still, add WHERE category IN ('lighting', 'furniture') as well, so untouched rows aren't rewritten at all. The same pattern handles "set a specific value per id": SET stock = CASE id WHEN 1 THEN 35 WHEN 2 THEN 10 END WHERE id IN (1, 2).

UPDATE and NULL

Set a column to NULL

Assign it like any other value. In SET, the = is an assignment, so = NULL is correct:

UPDATE products
SET discount = NULL
WHERE id = 4;

If the column is declared NOT NULL, this fails with a constraint error. SET discount = DEFAULT puts the column's default back instead (not available in SQLite).

WHERE column = NULL updates nothing

In WHERE, the = is a comparison, and nothing is ever equal to NULL:

UPDATE products
SET supplier_id = 1
WHERE supplier_id = NULL;
UPDATE 0

No error, no rows. Use IS NULL:

UPDATE products
SET supplier_id = 1
WHERE supplier_id IS NULL;
UPDATE 1

Math with a NULL erases the value

Any arithmetic with NULL gives NULL. Subtracting the discount from every price looks harmless:

UPDATE products
SET price = price - discount;
 id |     name      | price
----+---------------+--------
  1 | Desk lamp     |
  2 | Floor lamp    |  79.00
  3 | Office chair  |
  4 | Standing desk | 400.00
  5 | Notebook      |
  6 | Gel pen       |
(6 rows)

Four products had no discount, and now they have no price. Either filter them out (WHERE discount IS NOT NULL) or treat a missing discount as zero with COALESCE:

UPDATE products
SET price = price - COALESCE(discount, 0);

The same applies to text: in PostgreSQL and SQLite, name || NULL is NULL.

Undo an UPDATE with a transaction

A committed UPDATE can't be undone, and the old values are gone; the only way back is a backup. But an update inside a transaction can be checked and thrown away before it becomes permanent:

BEGIN;

UPDATE products SET price = 0 WHERE category = 'lighting';
SELECT COUNT(*) AS free_products FROM products WHERE price = 0;
UPDATE 2

 free_products
---------------
             2
(1 row)

Not what you meant? Throw it away:

ROLLBACK;
SELECT COUNT(*) AS free_products FROM products WHERE price = 0;
 free_products
---------------
             0
(1 row)

The old prices are back. If the result looks right, run COMMIT; instead. SQL Server spells it BEGIN TRANSACTION; MySQL accepts BEGIN or START TRANSACTION.

Many tools run in autocommit mode, where every statement commits the moment it finishes, so you must type BEGIN yourself before the update. And keep the transaction short: until you commit or roll back, the updated rows are locked and other sessions trying to change them will wait.

See which rows changed (RETURNING and OUTPUT)

PostgreSQL and SQLite (3.35 and later) can hand back the updated rows, with their new values, in the same statement:

UPDATE products
SET price = price * 2
WHERE category = 'stationery'
RETURNING id, name, price;
 id |   name   | price
----+----------+-------
  5 | Notebook |  9.00
  6 | Gel pen  |  2.40
(2 rows)

UPDATE 2

PostgreSQL 18 added RETURNING old.price, new.price to get the before and after values side by side. SQL Server has had that for a long time with OUTPUT, which reads the virtual deleted (before) and inserted (after) tables and goes before WHERE:

UPDATE products
SET price = price * 2
OUTPUT deleted.price AS old_price, inserted.price AS new_price
WHERE category = 'stationery';

MySQL has no equivalent; run a SELECT with the same WHERE inside the transaction.

UPDATE with a subquery in WHERE

Often the rows to update are defined by another table. "Zero the stock of everything from Canadian suppliers" needs the suppliers table to know who is Canadian. The portable way, which works in every database, is a subquery in the WHERE clause:

UPDATE products
SET stock = 0
WHERE supplier_id IN (
  SELECT id FROM suppliers WHERE country = 'CA'
);
UPDATE 2

The subquery can also be correlated, comparing each row with something computed from the same table. This marks every product priced above its category's average:

UPDATE products
SET discount = 5.00
WHERE price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = products.category
);
UPDATE 3

The floor lamp, standing desk and notebook are each the pricier item in their category. MySQL rejects this one with error 1093 ("You can't specify target table 'products' for update in FROM clause") because the subquery reads the table being updated; wrap the subquery in a derived table or use the join form there.

Update from another table (UPDATE from SELECT)

The other common job: the new values live in a second table. Here new_prices holds new prices for products 1, 4 and 5, and we want to copy them into products.

The portable way: a correlated subquery (and its trap)

A subquery in SET looks up the new price for each row. This version is wrong, and it's one of the most common UPDATE bugs:

UPDATE products
SET price = (
  SELECT np.price
  FROM new_prices np
  WHERE np.product_id = products.id
);
UPDATE 6

 id |     name      | price
----+---------------+--------
  1 | Desk lamp     |  26.00
  2 | Floor lamp    |
  3 | Office chair  |
  4 | Standing desk | 399.00
  5 | Notebook      |   4.75
  6 | Gel pen       |
(6 rows)

There's no WHERE on the UPDATE, so all six rows were updated. For the three products with no row in new_prices, the subquery found nothing, returned NULL, and their prices were erased. Restrict the update to rows that have a match:

UPDATE products
SET price = (
  SELECT np.price
  FROM new_prices np
  WHERE np.product_id = products.id
)
WHERE EXISTS (
  SELECT 1
  FROM new_prices np
  WHERE np.product_id = products.id
);
UPDATE 3

 id |     name      | price
----+---------------+--------
  1 | Desk lamp     |  26.00
  2 | Floor lamp    |  89.00
  3 | Office chair  | 150.00
  4 | Standing desk | 399.00
  5 | Notebook      |   4.75
  6 | Gel pen       |   1.20
(6 rows)

This form runs unchanged in PostgreSQL, SQLite, MySQL, SQL Server and Oracle. (Wrapping the subquery in COALESCE(( ... ), price) also keeps the old price, but still rewrites every row.) If the subquery returns more than one row for a product, the statement fails in most databases ("more than one row returned by a subquery used as an expression" in PostgreSQL); SQLite quietly takes the first one.

The shorter way: UPDATE ... FROM

Writing the lookup twice is clumsy, so most databases have a join syntax. PostgreSQL and SQLite (3.33 and later) use FROM:

UPDATE products
SET price = np.price
FROM new_prices np
WHERE np.product_id = products.id;
UPDATE 3

Same result as the EXISTS version: only the three matched rows change, because the join condition in WHERE acts as an inner join. The source doesn't have to be a table. A subquery in FROM (or a CTE in front of the UPDATE) lets you update from an aggregate, which is what people usually mean by "update from select":

UPDATE products
SET discount = 5.00
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) a
WHERE a.category = products.category
  AND products.price > a.avg_price;
UPDATE 3

UPDATE with JOIN in each database

Standard SQL has no join in UPDATE, so every database invented its own. All of these do the same price update from new_prices.

MySQL and MariaDB

The join goes right after UPDATE, before SET:

UPDATE products AS p
JOIN new_prices AS np ON np.product_id = p.id
SET p.price = np.price;

Use LEFT JOIN here and unmatched products are updated too, with np.price as NULL, which recreates the trap above. Stick to an inner join unless you add COALESCE.

SQL Server

Update the alias, and put the join in a FROM clause after SET:

UPDATE p
SET p.price = np.price
FROM products AS p
JOIN new_prices AS np ON np.product_id = p.id;

PostgreSQL

The target table is named once, after UPDATE; other tables go in FROM and the join condition in WHERE:

UPDATE products AS p
SET price = np.price
FROM new_prices AS np
WHERE np.product_id = p.id;
UPDATE 3

Two PostgreSQL gotchas. Don't repeat the target table in FROM (that's a self-join, and usually updates every row). And don't qualify the column on the left of SET: SET p.price = np.price fails with column "p" of relation "products" does not exist.

SQLite

SQLite 3.33 and later accept the PostgreSQL form above, with one difference: an alias on the target table needs the word AS. UPDATE products p SET ... gives near "p": syntax error; UPDATE products AS p SET ... works. Older SQLite versions need the correlated subquery.

Oracle

Before version 23ai Oracle has no join in UPDATE: use the correlated subquery with WHERE EXISTS, or MERGE. 23ai added a PostgreSQL-style FROM clause.

MERGE: the standard alternative

MERGE is in the SQL standard and works in SQL Server, Oracle and PostgreSQL 15 and later (not MySQL or SQLite):

MERGE INTO products p
USING new_prices np
  ON np.product_id = p.id
WHEN MATCHED THEN
  UPDATE SET price = np.price;
MERGE 3

When the join matches more than one row

Suppose new_prices has two rows for the desk lamp, 26.00 and 30.00. A joined update doesn't fail; it picks one. Running the identical UPDATE ... FROM gave 26.00 in PostgreSQL and 30.00 in SQLite. Neither is wrong: the result is officially unpredictable, and the same is true of MySQL and SQL Server joins. (MERGE, and the subquery form outside SQLite, raise an error instead, which is safer.) Make sure the source has one row per target row, by aggregating it or picking the latest with ROW_NUMBER() first.

Upsert: update the row, or insert it if it's missing

UPDATE only changes rows that exist; if nothing matches, nothing happens. "Update it, or create it if it isn't there" is called an upsert, and it's written as an INSERT with a fallback. In PostgreSQL and SQLite (3.24 and later):

INSERT INTO products (id, name, category, price, stock)
VALUES (5, 'Notebook', 'stationery', 4.95, 280)
ON CONFLICT (id) DO UPDATE
SET price = excluded.price,
    stock = excluded.stock;
 id |   name   | price | stock
----+----------+-------+-------
  5 | Notebook |  4.95 |   280
(1 row)

Product 5 already existed, so its price and stock were updated; excluded refers to the row that failed to insert. With an id that doesn't exist yet, the same statement inserts a new row. MySQL's version is INSERT ... ON DUPLICATE KEY UPDATE price = VALUES(price) (or, from 8.0.19, VALUES (...) AS new ON DUPLICATE KEY UPDATE price = new.price). SQL Server and Oracle use MERGE with both a WHEN MATCHED THEN UPDATE and a WHEN NOT MATCHED THEN INSERT branch. All of them need a primary key or unique constraint to detect the conflict.

Update in batches (LIMIT and TOP)

Updating millions of rows in one statement holds locks for a long time and can balloon the transaction log. The fix is to update a few thousand rows at a time and repeat until the count is zero.

-- MySQL
UPDATE products SET stock = 10 WHERE stock = 0 ORDER BY id LIMIT 5000;

-- SQL Server
UPDATE TOP (5000) products SET stock = 10 WHERE stock = 0;

PostgreSQL has no LIMIT on UPDATE. Pick the ids in a subquery instead (this works in PostgreSQL and SQLite):

UPDATE products
SET stock = 10
WHERE id IN (
  SELECT id FROM products
  WHERE stock = 0
  ORDER BY id
  LIMIT 1
);
UPDATE 1

Two products are out of stock; only the first (the floor lamp) was restocked, and the gel pen is left for the next batch. This works because the update itself makes the row stop matching stock = 0. If your update doesn't change the column you filter on, walk through the table by id range instead (WHERE id > 0 AND id <= 5000, then the next 5,000).

"0 rows updated": matched vs changed

An UPDATE that matches nothing is not an error:

UPDATE products SET price = 1 WHERE id = 99;
UPDATE 0

If you expected a change, the WHERE is the problem: a wrong id, a = NULL, a case mismatch in a text comparison, or trailing spaces in the data. Run the WHERE in a SELECT.

There's a second, subtler case. Setting a column to the value it already has:

UPDATE products SET price = 24.00 WHERE id = 1;
UPDATE 1

PostgreSQL, SQLite and SQL Server count the row as updated (and really do rewrite it, firing triggers). MySQL notices nothing changed and reports Rows matched: 1 Changed: 0, so an application that checks "rows affected" to see whether a record exists can get 0 for a row that's there. Either way, your application code should check the affected-row count after an update rather than assume it worked.

When UPDATE fails: constraint errors

The new values must satisfy every constraint on the table, or the whole statement is rejected and no rows change.

Foreign key. Pointing a product at a supplier that doesn't exist:

UPDATE products SET supplier_id = 9 WHERE id = 1;
ERROR:  insert or update on table "products" violates foreign key constraint "products_supplier_id_fkey"
DETAIL:  Key (supplier_id)=(9) is not present in table "suppliers".

SQLite says FOREIGN KEY constraint failed, but only if you've run PRAGMA foreign_keys = ON; on the connection; by default it doesn't check and happily stores the orphan value.

Unique or primary key. Giving a row a key that's already taken:

UPDATE products SET id = 2 WHERE id = 1;
ERROR:  duplicate key value violates unique constraint "products_pkey"
DETAIL:  Key (id)=(2) already exists.

(SQLite: UNIQUE constraint failed: products.id.) You can update a primary key, but it's rarely a good idea, because every foreign key pointing at it has to change too.

NOT NULL and CHECK constraints work the same way, as do data type errors such as putting text into a numeric column (SQLite, with its loose typing, will store it).

ON UPDATE: cascades and "last updated" timestamps

ON UPDATE isn't part of the UPDATE statement; it's something you declare on a table, and it means two different things.

On a foreign key, ON UPDATE CASCADE says what happens to child rows when the parent's key changes:

supplier_id INTEGER REFERENCES suppliers(id) ON UPDATE CASCADE

With that, changing a supplier's id from 2 to 20 automatically changes supplier_id on its products. Without it (the default is NO ACTION), the update is refused while products still reference the old id. Its sibling ON DELETE CASCADE is covered in the DELETE guide.

On a timestamp column in MySQL, ON UPDATE CURRENT_TIMESTAMP keeps a "last modified" time for you:

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

That's MySQL and MariaDB only. In PostgreSQL, SQL Server and SQLite you either set it yourself in every update (SET price = 27.50, updated_at = CURRENT_TIMESTAMP) or create a trigger that does.

UPDATE in MySQL, PostgreSQL, SQL Server, SQLite and Oracle

The basic UPDATE t SET ... WHERE ... is identical everywhere. The extras are not:

FeatureMySQLPostgreSQLSQL ServerSQLiteOracle
Update with another tableUPDATE t JOIN s ON ... SET ...UPDATE t SET ... FROM s WHERE ...UPDATE t SET ... FROM t JOIN s ON ...UPDATE t SET ... FROM s WHERE ... (3.33+)Subquery or MERGE; FROM in 23ai
MERGENo15+YesNoYes
UpsertON DUPLICATE KEY UPDATEON CONFLICT ... DO UPDATEMERGEON CONFLICT ... DO UPDATE (3.24+)MERGE
Return updated rowsNoRETURNINGOUTPUT inserted.*, deleted.*RETURNING (3.35+)RETURNING ... INTO (PL/SQL)
Limit rows updatedORDER BY ... LIMIT nSubquery with LIMITTOP (n)Subquery with LIMITSubquery with FETCH FIRST
SET (a, b) = (x, y)NoYesNoYesWith a subquery
SET seesNew values, left to rightOld valuesOld valuesOld valuesOld values
Unchanged row countedNo ("Changed: 0")YesYesYesYes
Alias on target tableYesYesIn the FROM formOnly with ASYes

MySQL error 1175. In MySQL Workbench, an update whose WHERE doesn't use a key column fails with "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column". That's Workbench protecting you from overwriting the whole table. Update by primary key, or turn it off for the session with SET SQL_SAFE_UPDATES = 0; and back on afterwards.

Performance: indexes, locks and big updates

An UPDATE has to find its rows first, exactly like a SELECT, so the same index rules apply. In SQLite:

EXPLAIN QUERY PLAN UPDATE products SET stock = 0 WHERE id = 3;
SEARCH products USING INTEGER PRIMARY KEY (rowid=?)
EXPLAIN QUERY PLAN UPDATE products SET stock = 0 WHERE category = 'lighting';
SCAN products

Updating by primary key jumps straight to the row; filtering on the unindexed category reads the whole table. After CREATE INDEX idx_products_category ON products(category); the second plan becomes SEARCH products USING INDEX idx_products_category (category=?). A few other things to know:

  • Updated rows are locked until you commit. A long transaction that updated a popular row makes everyone else wait. On a table scan, some databases lock far more rows than end up changing.
  • Indexes cut both ways. An index on the WHERE column makes the update faster; every index that includes a column you change has to be updated too.
  • Skip rows that already have the value. UPDATE products SET stock = 0 WHERE stock <> 0 writes only the rows that need it. Without that filter, PostgreSQL and SQL Server rewrite every row anyway.
  • PostgreSQL writes a new copy of each updated row and leaves the old one for VACUUM to clean up, so a whole-table update can temporarily double the table's size.
  • Huge single updates: use batches.

Common UPDATE mistakes

  • Forgetting WHERE, or running only the first lines of a selected statement. Preview with SELECT, and wrap risky updates in BEGIN ... ROLLBACK/COMMIT.
  • Updating by a non-unique value (a name) when you meant one row. Use the primary key.
  • AND instead of commas in SET: a syntax error in most databases, a silent wrong value in MySQL.
  • A correlated subquery in SET with no WHERE EXISTS: every unmatched row is set to NULL.
  • CASE without ELSE: same result, rows matching no branch become NULL.
  • WHERE col = NULL: updates nothing. Use IS NULL.
  • Arithmetic with a nullable column: price - discount is NULL when the discount is. Use COALESCE.
  • Mixing AND and OR without parentheses: updates far more than you meant.
  • A joined update where the source has several rows per target: you get one of them, and not the same one in every database.
  • Read, calculate in the app, write back: two users doing it at once lose one of the changes. Use SET stock = stock - 1.
  • Assuming the update worked: check the affected-row count; 0 rows is not an error.

FAQ

Is UPDATE a command in SQL?

Yes. UPDATE is the SQL statement that changes existing rows. It's part of DML (data manipulation language), alongside INSERT and DELETE.

What is the syntax for UPDATE?

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;. SET lists the new values, WHERE chooses the rows. Without WHERE, all rows are updated.

How do I update multiple columns in SQL?

List them after one SET, separated by commas: UPDATE products SET price = 79, stock = 25 WHERE id = 2;.

How do I update multiple rows in SQL?

Write a WHERE that matches all of them, such as WHERE id IN (1, 2) or WHERE category = 'lighting'. To give each row a different value, use CASE in the SET clause or join to a table of new values.

Can I use a JOIN in an UPDATE?

Yes, but the syntax depends on the database. MySQL: UPDATE t JOIN s ON ... SET .... SQL Server: UPDATE t SET ... FROM t JOIN s ON .... PostgreSQL and SQLite: UPDATE t SET ... FROM s WHERE .... A correlated subquery with WHERE EXISTS works in all of them.

How do I update a table from a SELECT?

Put the SELECT in the FROM clause (PostgreSQL, SQLite, SQL Server) or in a JOIN (MySQL) and match it to the target table, or use a correlated subquery in SET. See update from another table.

Can I undo an UPDATE?

Only if it ran inside a transaction that hasn't been committed: run ROLLBACK;. Once committed (and in autocommit mode every statement commits immediately), the old values are gone and you need a backup.

What happens if I run UPDATE without WHERE?

Every row in the table gets the new value. The database doesn't warn you (MySQL Workbench's safe update mode is the exception).

What is the difference between UPDATE and ALTER?

UPDATE changes the data in rows. ALTER TABLE changes the table's structure: adding, renaming or dropping columns and constraints.

What is the difference between UPDATE and INSERT?

INSERT adds new rows; UPDATE changes rows that already exist and does nothing if none match. An upsert does whichever is needed.

Does UPDATE lock the table?

Normally it locks only the rows it changes, until the transaction ends. Other sessions can still read (in PostgreSQL, MySQL InnoDB and Oracle) and can update other rows. SQLite is the exception: it allows one writer at a time for the whole database.

Practice the WHERE clause first

An UPDATE is only as good as its WHERE clause, and that's exactly what the free interactive lessons train: start with WHERE equals, then AND, IN, NULL and CASE. They run in your browser on a real SQLite database with no signup. Then practise writing filters in the SQL practice problems, see more statements in the SQL query examples, and keep the SQL cheat sheet open for the syntax. The companion guides cover adding rows with INSERT INTO and removing them with DELETE.

When you're working on a real database, Beekeeper Studio is a free, open source SQL editor for PostgreSQL, MySQL, SQLite and SQL Server, where you can preview rows, edit values directly in the table view and run updates inside a transaction.