UPDATE

Learning Focus

Use this lesson to confidently use UPDATE statements in DuckDB to modify rows safely.

Basic Syntax

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

Always Use WHERE

Running UPDATE without WHERE will modify every row in the table. Always confirm your WHERE clause with a SELECT first.

Examples

Example 1: Update a Single Column

-- Give Alice a 10% raise
UPDATE employees
SET salary = salary * 1.10
WHERE name = 'Alice Johnson';

Example 2: Update Multiple Columns

UPDATE employees
SET department = 'Product', salary = 90000.00
WHERE id = 3;

Example 3: Safe Pattern — Preview First

-- 1. Preview affected rows
SELECT id, name, salary FROM employees WHERE department = 'Marketing';

-- 2. Apply update
UPDATE employees
SET salary = salary + 5000
WHERE department = 'Marketing';

Example 4: Update from Another Table

-- DuckDB supports UPDATE FROM
UPDATE employees
SET salary = raises.new_salary
FROM raises
WHERE employees.id = raises.employee_id;

Example 5: Update with CASE

UPDATE employees
SET salary = CASE
    WHEN department = 'Engineering' THEN salary * 1.15
    WHEN department = 'Marketing'   THEN salary * 1.10
    ELSE salary * 1.05
END;

Common Pitfalls

Pitfall	Consequence	Prevention
Missing WHERE clause	Updates all rows	Always preview with SELECT before UPDATE
Update with wrong type	Type conversion error	Cast values explicitly: CAST(val AS type)
DuckDB doesn't support MySQL's multi-table UPDATE	Syntax error	Use UPDATE ... FROM subquery pattern

Quick Reference

UPDATE t SET col = val WHERE condition;
UPDATE t SET col1 = v1, col2 = v2 WHERE id = 1;
UPDATE t SET col = col * 1.1 WHERE dept = 'Eng';
UPDATE t SET col = val FROM other WHERE t.id = other.id;

What's Next

• Next: DELETE - Remove rows from a table.
• Module Overview - Return to this module index.