UPDATE — Change Existing Data

Dhoni hits a quick 50 and his runs must change from 350 to 400. Warner moves from DC to SRH. Data changes all the time — UPDATE is how you keep it correct.

Definition

UPDATE is a DML statement that modifies the values of one or more columns in existing rows of a table. The SET clause gives the new values, and the WHERE clause decides which rows are changed. Without WHERE, every row in the table is updated.

• Existing rows — UPDATE never adds or removes rows; the row count stays the same.
• SET — column = new_value, separated by commas for several columns.
• WHERE — the filter. Only rows where the condition is TRUE are changed.

Syntax

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

NOTE: Always use WHERE to avoid updating all rows unintentionally.

Example — change one value

UPDATE players
SET runs = 400
WHERE id = 4;

SELECT * FROM players;

After — Dhoni: 350 → 400 runs:

Step by step: 1️⃣ WHERE finds the row: id = 4 → 2️⃣ SET changes runs to 400. Only MS Dhoni's row changes.

Example — change many columns

UPDATE players
SET team = 'SRH',
    runs = 760
WHERE id = 5;

After — Warner: new team + runs:

Step by step: 1️⃣ WHERE finds id = 5 → 2️⃣ SET changes 2 columns. Separate columns with commas — both cells on one row change.

Example — calculate from the old value

The new value can use the current value of the column:

UPDATE players
SET runs = runs + 50
WHERE team = 'MI';

Both MI players get +50 (500 → 550, 20 → 70). The WHERE matched 2 rows, so SQL Server says (2 rows affected).

DANGER — UPDATE without WHERE

UPDATE players
SET runs = 0;

After — ALL rows changed!

1️⃣ No WHERE = no filter → 2️⃣ EVERY row is changed! Every player's runs became 0. Never forget WHERE!

Update using another table

Use a subquery in WHERE (works in every database):

-- +10 runs for players whose team is based in Mumbai
UPDATE players
SET runs = runs + 10
WHERE team IN (SELECT team FROM teams WHERE city = 'Mumbai');

SQL Server also supports UPDATE with a JOIN using FROM:

UPDATE p
SET p.runs = p.runs + 10
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team
WHERE t.city = 'Mumbai';

Common mistakes

• ❌ Forgetting WHERE → every row changed. ✅ Write the WHERE first, and check "rows affected".
• ❌ SET team = 'SRH' AND runs = 760 ✅ Use a comma: SET team = 'SRH', runs = 760. (With AND, SQL Server raises a syntax error; MySQL silently sets team to a 0/1 value!)
• ❌ WHERE name = 'Dhoni' when the stored name is 'MS Dhoni' → 0 rows affected. ✅ Filter by the primary key (id = 4) whenever possible.
• ❌ WHERE runs = NULL ✅ WHERE runs IS NULL — = NULL never matches.
• ❌ Using UPDATE to add a new player. ✅ UPDATE only changes existing rows; use INSERT.

Interview tip

Q: What happens if you run an UPDATE without a WHERE clause? A: Every row in the table is updated with the new value(s). That's why you should always write the WHERE, test it with a SELECT first, and ideally run the change inside a transaction so you can ROLLBACK if the rows-affected count looks wrong.