Ad – 728×90
✏️ Data Modification

SQL UPDATE – Modifying Existing Data

UPDATE modifies existing rows in a table. It is one of the most commonly used SQL operations — and one of the most dangerous if you forget the WHERE clause, which would update every row in the table. Always test your UPDATE with a SELECT first.

⏱️ 20 min read 🎯 Beginner 📅 Updated 2026

Basic UPDATE Syntax

SQL
UPDATE table_name
SET    column1 = value1
WHERE  condition;   -- ⚠️ without WHERE, ALL rows are updated!
SQL
-- Update a single employee's salary
UPDATE employees
SET    salary = 75000
WHERE  id = 42;

-- Update status for all expired subscriptions
UPDATE subscriptions
SET    status = 'expired'
WHERE  end_date < CURRENT_DATE
  AND  status   = 'active';
⚠️
Always test UPDATE with SELECT first

Before running any UPDATE, run the equivalent SELECT with the same WHERE clause to verify exactly which rows will be affected: SELECT * FROM employees WHERE id = 42. Only when the SELECT returns the expected rows should you change it to UPDATE. This habit prevents accidental data loss.

UPDATE with Calculations

The SET clause can use expressions, including references to the current value of the column being updated:

SQL
-- Give Engineering a 10% raise
UPDATE employees
SET    salary = salary * 1.10
WHERE  department = 'Engineering';

-- Increment view count
UPDATE articles
SET    view_count = view_count + 1
WHERE  id = 789;

-- Apply a discount: reduce price by 20%
UPDATE products
SET    price = ROUND(price * 0.80, 2),
       updated_at = NOW()
WHERE  category = 'Seasonal'
  AND  stock > 100;
Ad – 336×280

Updating Multiple Columns

Separate multiple SET assignments with commas:

SQL
-- Update multiple fields for a user profile change
UPDATE users
SET    first_name  = 'Robert',
       last_name   = 'Smith',
       email       = 'robert.smith@example.com',
       updated_at  = NOW()
WHERE  id = 101;

-- Reset failed login counters for all users
UPDATE users
SET    failed_logins = 0,
       locked_until  = NULL
WHERE  locked_until  < NOW();

UPDATE with JOIN

Update rows in one table based on values from another table:

SQL
-- MySQL / SQL Server: UPDATE with JOIN syntax
UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.location = d.office_location
WHERE d.region = 'APAC';

-- PostgreSQL: UPDATE with FROM clause
UPDATE employees e
SET    location = d.office_location
FROM   departments d
WHERE  e.dept_id = d.id
  AND  d.region  = 'APAC';

-- Standard SQL (works everywhere): UPDATE with subquery
UPDATE employees
SET    location = (
           SELECT office_location
           FROM   departments
           WHERE  departments.id = employees.dept_id
       )
WHERE  dept_id IN (
           SELECT id FROM departments WHERE region = 'APAC'
       );

Safety Practices

PracticeWhy it matters
Test with SELECT first Verify which rows match the WHERE condition before changing them.
Use transactions Wrap UPDATE in BEGIN/COMMIT so you can ROLLBACK if something goes wrong.
Backup before bulk updates For large-scale changes, dump the table or use CREATE TABLE backup AS SELECT ....
Update in batches For millions of rows, use LIMIT n (MySQL) or a WHERE id range to avoid locking the whole table.
SQL
-- Safe pattern: wrap in transaction so you can rollback
BEGIN;

-- Verify first
SELECT id, salary FROM employees WHERE department = 'Engineering';

-- Apply the update
UPDATE employees
SET    salary = salary * 1.10
WHERE  department = 'Engineering';

-- Check the result
SELECT id, salary FROM employees WHERE department = 'Engineering';

-- If happy:
COMMIT;
-- If not: ROLLBACK;

Summary

📋 Summary

  • UPDATE table SET col = val WHERE condition — always include WHERE.
  • The SET clause can reference the column's current value for calculations.
  • Update multiple columns by separating assignments with commas.
  • UPDATE with JOIN allows updating based on data in another table (syntax varies by database).
  • DANGER: UPDATE without WHERE updates every row. Always test with SELECT first and use transactions for safety.
🗄️ INSERT Data 🗄️ DELETE Data 🗄️ Transactions 🗄️ UPSERT

FAQ

Can UPDATE return the modified rows like RETURNING? +

Yes — PostgreSQL supports UPDATE ... RETURNING col1, col2 to return the updated row values. SQL Server uses OUTPUT INSERTED.col, DELETED.col to see both the old and new values. MySQL does not have a native RETURNING clause for UPDATE; you need a subsequent SELECT to read the updated rows.

What is the difference between UPDATE and REPLACE? +

REPLACE (MySQL-specific) does a DELETE followed by an INSERT if the row already exists — it removes the entire old row and creates a new one. This means all columns not specified get their default values. UPDATE only changes the columns listed in SET, leaving others untouched. Prefer UPDATE for modifying existing data; use REPLACE / ON DUPLICATE KEY UPDATE for upsert semantics.

How do I update rows based on a complex condition from another table? +

Use UPDATE with a subquery in WHERE or a JOIN (syntax varies by database). For complex conditions, a CTE can clarify the logic: WITH target AS (SELECT id FROM ... WHERE complex_condition) UPDATE t SET ... FROM target WHERE t.id = target.id (PostgreSQL). This pattern makes it easy to first SELECT from the CTE to verify the targets, then switch to UPDATE.

Ad – 728×90