UPDATE with expressions in SQL - Deep Dive

Overview - UPDATE with expressions

What is it?

The UPDATE statement in SQL changes existing data in a table. Using expressions in UPDATE lets you modify values based on calculations or other columns. Instead of just setting a column to a fixed value, you can add, subtract, or use functions to update it dynamically. This makes data changes smarter and more flexible.

Why it matters

Without UPDATE expressions, you would have to manually calculate new values outside the database and then update them, which is slow and error-prone. Expressions let the database do the math instantly and correctly, saving time and reducing mistakes. This is crucial for keeping data accurate and up-to-date in real-world applications like inventory, finance, or user profiles.

Where it fits

Before learning UPDATE with expressions, you should understand basic SQL SELECT and UPDATE commands. After this, you can learn about transactions, triggers, and stored procedures to automate and secure data changes.

Mental Model

Core Idea

UPDATE with expressions lets you change data by calculating new values directly inside the database.

Think of it like...

It's like adjusting your bank account balance by adding or subtracting amounts on your calculator before writing the new balance in your ledger.

┌───────────────┐
│   Table Row   │
│ ┌───────────┐ │
│ │ Column A  │ │
│ │ Column B  │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ UPDATE table SET ColumnA =   │
│ ColumnA + 10 WHERE ...       │
└─────────────────────────────┘
       │
       ▼
┌───────────────┐
│ Updated Row   │
│ ColumnA = old│ + 10          │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic UPDATE statement syntax

Concept: Learn how to change data in a table using UPDATE with fixed values.

The UPDATE command changes one or more rows in a table. You specify the table, the column(s) to change, and the new value(s). For example: UPDATE employees SET salary = 50000 WHERE id = 1; This sets the salary of the employee with id 1 to 50000.

Result

The specified rows have their columns changed to the new fixed values.

Understanding the basic UPDATE syntax is essential before adding expressions, as it shows how data changes happen in SQL.

2

Foundation

What are expressions in SQL?

Concept: Expressions are calculations or operations that produce a value, used in SQL statements.

Expressions can be simple math like addition or subtraction, or functions like CONCAT or UPPER. For example: SELECT salary + 1000 FROM employees; This adds 1000 to each salary in the result. Expressions can combine columns, constants, and functions.

Result

You get calculated values instead of fixed ones when using expressions.

Knowing what expressions are helps you see how UPDATE can use them to change data dynamically.

3

Intermediate

Using arithmetic expressions in UPDATE

🤔Before reading on: do you think you can increase a column value by 10 using UPDATE? Commit to yes or no.

Concept: You can use arithmetic expressions to modify column values relative to their current state.

Instead of setting a column to a fixed number, you can add or subtract from its current value: UPDATE products SET stock = stock - 1 WHERE product_id = 100; This reduces the stock by 1 for product 100.

Result

The stock column decreases by 1 for the matching row.

Using expressions in UPDATE lets you adjust data based on its current value, which is more powerful than fixed assignments.

4

Intermediate

Updating columns using other columns

🤔Before reading on: can you set one column's value based on another column in the same row? Commit to yes or no.

Concept: You can assign a column a value calculated from other columns in the same row.

For example, to set a discounted price based on the original price: UPDATE products SET discounted_price = price * 0.9 WHERE category = 'Books'; This sets discounted_price to 90% of price for books.

Result

discounted_price is updated dynamically based on price for each matching row.

This shows how expressions can relate columns within a row, enabling complex data transformations.

5

Intermediate

Using SQL functions in UPDATE expressions

🤔Before reading on: do you think you can use functions like UPPER() in UPDATE? Commit to yes or no.

Concept: SQL functions can be used in UPDATE to transform data, like changing text case or dates.

For example, to capitalize all customer names: UPDATE customers SET name = UPPER(name); This changes all names to uppercase letters.

Result

All customer names become uppercase.

Functions in UPDATE expressions allow powerful data formatting and manipulation directly in the database.

6

Advanced

Conditional expressions in UPDATE with CASE

🤔Before reading on: can you update a column differently based on conditions in the same query? Commit to yes or no.

Concept: The CASE expression lets you apply different updates depending on conditions within one UPDATE statement.

Example: UPDATE employees SET bonus = CASE WHEN performance = 'Excellent' THEN 1000 WHEN performance = 'Good' THEN 500 ELSE 0 END; This sets bonus based on performance rating.

Result

Each employee's bonus is updated according to their performance category.

Using CASE in UPDATE expressions enables complex, conditional data changes in a single query.

7

Expert

Avoiding pitfalls with expression side effects

🤔Before reading on: do you think the order of updates matters when using expressions that depend on updated columns? Commit to yes or no.

Concept: When updating multiple columns with expressions that depend on each other, the order and evaluation can affect results.

For example: UPDATE accounts SET balance = balance - 100, last_withdrawal = balance - 100 WHERE id = 1; Here, last_withdrawal uses the old balance, not the new one. To get the updated balance, you must repeat the expression or use a subquery.

Result

last_withdrawal is set to the old balance minus 100, not the new balance value.

Understanding evaluation order prevents subtle bugs when expressions depend on updated columns within the same statement.

Under the Hood

When you run an UPDATE with expressions, the database engine reads the current row values, calculates the new values using the expressions, and writes the updated data back to the storage. This happens row by row, ensuring atomicity and consistency. Expressions are evaluated using the current row's data snapshot before any changes are saved.

Why designed this way?

This design ensures that updates are consistent and predictable. Calculating expressions inside the database avoids data transfer overhead and keeps logic centralized. Alternatives like updating data externally would be slower and prone to errors. The row-by-row evaluation guarantees that each update uses stable data.

┌───────────────┐
│   Table Row   │
│ ┌───────────┐ │
│ │ Column A  │ │
│ │ Column B  │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Evaluate expressions using   │
│ current row values           │
└──────────────┬──────────────┘
               │
               ▼
┌─────────────────────────────┐
│ Write updated values back to │
│ the table                   │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UPDATE with expressions change all rows if no WHERE clause is given? Commit yes or no.

Common Belief: UPDATE with expressions only changes rows that meet some condition or only the rows you want.

Reality: If you omit the WHERE clause, UPDATE changes every row in the table, applying the expression to all.

Why it matters: Accidentally updating all rows can cause massive data corruption or loss, requiring costly recovery.

Quick: Does an expression in UPDATE use the new value of a column if that column is updated earlier in the same statement? Commit yes or no.

Common Belief: Expressions in UPDATE use the latest updated values within the same statement.

Reality: Expressions use the original values from before the UPDATE started, not intermediate updated values.

Why it matters: Assuming updated values are available can lead to wrong calculations and unexpected results.

Quick: Can you use subqueries inside UPDATE expressions in all SQL databases? Commit yes or no.

Common Belief: All SQL databases allow subqueries inside UPDATE expressions freely.

Reality: Some databases restrict or limit subqueries in UPDATE expressions for performance or syntax reasons.

Why it matters: Trying unsupported subqueries causes errors and breaks applications if not tested properly.

Quick: Does using functions in UPDATE expressions always guarantee the same result for every row? Commit yes or no.

Common Belief: Functions in UPDATE expressions produce consistent results for all rows.

Reality: Functions like RANDOM() or CURRENT_TIMESTAMP produce different results per row, causing non-uniform updates.

Why it matters: Not knowing this can cause confusion when expecting identical updates but getting varied data.

Expert Zone

1

Some databases optimize UPDATE expressions by evaluating them once if they are constant, improving performance.

2

Using expressions that reference other columns can cause implicit type conversions, which may affect precision or cause errors.

3

Complex expressions in UPDATE can slow down queries; indexing and query plans matter for performance.

When NOT to use

Avoid complex UPDATE expressions when you need to update millions of rows frequently; consider batch processing or ETL tools instead. Also, if updates depend on external data, use application logic or stored procedures for better control.

Production Patterns

In production, UPDATE with expressions is used for inventory adjustments, recalculating scores, applying discounts, and timestamping changes. Often combined with transactions to ensure data integrity and with triggers to automate related updates.

Connections

Functional Programming

Both use expressions to transform data immutably and declaratively.

Understanding how expressions define transformations helps grasp how SQL UPDATE applies changes without side effects until committed.

Spreadsheet Formulas

UPDATE expressions are like formulas in spreadsheet cells that recalculate values based on other cells.

Knowing spreadsheet formulas clarifies how SQL expressions dynamically compute new values from existing data.

Bank Account Ledger

Both track changes by applying calculations to current balances or values.

Seeing UPDATE as ledger adjustments helps understand the importance of atomic, consistent updates.

Common Pitfalls

#1 Updating all rows unintentionally by missing WHERE clause.

Wrong approach:

UPDATE employees SET salary = salary + 1000;

Correct approach:

UPDATE employees SET salary = salary + 1000 WHERE department = 'Sales';

Root cause:

Forgetting to specify which rows to update causes the entire table to change.

#2 Using updated column values within the same UPDATE leading to wrong results.

Wrong approach:

UPDATE accounts SET balance = balance - 100, last_withdrawal = balance - 100 WHERE id = 1;

Correct approach:

UPDATE accounts SET balance = balance - 100, last_withdrawal = balance - 100 WHERE id = 1;

Root cause:

Misunderstanding that expressions use original values, not intermediate updated ones.

#3 Using unsupported subqueries in UPDATE expressions causing errors.

Wrong approach:

UPDATE products SET price = (SELECT AVG(price) FROM products) WHERE category = 'Books';

Correct approach:

WITH avg_price AS (SELECT AVG(price) AS avg FROM products) UPDATE products SET price = (SELECT avg FROM avg_price) WHERE category = 'Books';

Root cause:

Not knowing database-specific syntax restrictions on subqueries in UPDATE.

Key Takeaways

UPDATE with expressions lets you change data based on calculations using current row values.

Expressions can include arithmetic, functions, other columns, and conditional logic for flexible updates.

The database evaluates expressions using original row data before writing changes, ensuring consistency.

Missing WHERE clauses or misunderstanding evaluation order can cause serious data errors.

Mastering UPDATE expressions is essential for efficient, accurate, and powerful data manipulation in SQL.

Practice

1. What does the SQL statement UPDATE employees SET salary = salary + 500 do?

easy

A. Sets all salaries to 500

B. Increases the salary of all employees by 500

C. Deletes employees with salary 500

D. Creates a new employee with salary 500

Solution

1. Step 1: Understand the UPDATE statement

   The statement updates the 'salary' column in the 'employees' table.

2. Step 2: Analyze the expression used

   It adds 500 to the current salary value for every row because no WHERE clause limits the rows.

3. Final Answer:

   Increases the salary of all employees by 500 -> Option B

4. Quick Check:

   salary = salary + 500 [OK]

Hint: No WHERE means update all rows with the expression [OK]

Common Mistakes:

• Thinking it sets salary to 500 instead of adding
• Confusing UPDATE with DELETE
• Assuming it adds 500 only to some rows without WHERE

2. Which of the following is the correct syntax to increase the price by 10% only for products in category 'Books'?

easy

A. UPDATE products SET price = price * 1.10 WHERE category = 'Books';

B. UPDATE products SET price = price + 10% WHERE category = 'Books';

C. UPDATE products SET price = price + 0.10 WHERE category = 'Books';

D. UPDATE products SET price = price * 10 WHERE category = 'Books';

Solution

1. Step 1: Identify correct expression for 10% increase

   Multiplying price by 1.10 increases it by 10%.

2. Step 2: Check WHERE clause for category filtering

   WHERE category = 'Books' limits update to only books.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE category = 'Books'; -> Option A

4. Quick Check:

   price * 1.10 with WHERE category = 'Books' [OK]

Hint: Use multiplication by 1.10 for 10% increase [OK]

Common Mistakes:

• Using + 10% which is invalid syntax
• Adding 0.10 instead of 10%
• Multiplying price by 10 instead of 1.10

3. Given the table accounts with columns id, balance, what will be the balances after running:
UPDATE accounts SET balance = balance - 100 WHERE balance >= 500;
Initial balances: 400, 500, 600

medium

A. [400, 600, 700]

B. [300, 400, 500]

C. [400, 500, 600]

D. [400, 400, 500]

Solution

1. Step 1: Identify which rows meet the WHERE condition

   Only balances 500 and 600 are >= 500, so only these rows update.

2. Step 2: Subtract 100 from qualifying balances

   500 - 100 = 400, 600 - 100 = 500; 400 remains unchanged.

3. Final Answer:

   [400, 400, 500] -> Option D

4. Quick Check:

   Only balances >= 500 reduced by 100 [OK]

Hint: Only rows matching WHERE get updated [OK]

Common Mistakes:

• Updating all rows ignoring WHERE
• Subtracting 100 from all balances
• Confusing >= with >

4. What is wrong with this SQL statement?
UPDATE orders SET total = total + discount WHERE discount;

medium

A. The SET expression cannot use other columns

B. The UPDATE statement is missing a table name

C. The WHERE clause is invalid because it lacks a comparison

D. The total column cannot be updated

Solution

1. Step 1: Analyze the WHERE clause

   WHERE discount is incomplete; it needs a condition like discount > 0.

2. Step 2: Check other parts of the statement

   Table name and SET expression are correct; updating with another column is valid.

3. Final Answer:

   The WHERE clause is invalid because it lacks a comparison -> Option C

4. Quick Check:

   WHERE must have a condition, not just a column name [OK]

Hint: WHERE needs a condition, not just a column name [OK]

Common Mistakes:

• Assuming column names alone are valid WHERE conditions
• Thinking SET cannot use other columns
• Ignoring missing comparison in WHERE

5. You want to increase the salary by 5% for employees whose current salary is below the average salary. Which SQL statement correctly does this?

hard

A. UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees);

B. UPDATE employees SET salary = salary + 5 WHERE salary < AVG(salary);

C. UPDATE employees SET salary = salary * 1.05 WHERE salary > (SELECT AVG(salary) FROM employees);

D. UPDATE employees SET salary = salary * 0.95 WHERE salary < (SELECT AVG(salary) FROM employees);

Solution

1. Step 1: Use a subquery to get average salary

   The subquery (SELECT AVG(salary) FROM employees) returns the average salary.

2. Step 2: Apply 5% increase only to salaries below average

   WHERE salary < average filters employees below average; salary * 1.05 increases salary by 5%.

3. Final Answer:

   UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees); -> Option A

4. Quick Check:

   Use subquery in WHERE and multiply salary by 1.05 [OK]

Hint: Use subquery in WHERE and multiply salary by 1.05 [OK]

Common Mistakes:

• Using AVG(salary) without subquery
• Adding 5 instead of 5%
• Updating salaries above average instead of below
• Multiplying by 0.95 which decreases salary