UPDATE multiple columns in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL UPDATE statement do?

The UPDATE statement changes existing data in a table by modifying one or more columns for rows that meet a specified condition.

beginner

How do you update multiple columns in a single SQL UPDATE statement?

You list each column and its new value separated by commas after the SET keyword, like: SET column1 = value1, column2 = value2.

beginner

Why should you always include a WHERE clause in an UPDATE statement?

Without a WHERE clause, the UPDATE will change all rows in the table, which might cause unintended data loss or errors.

beginner

Example: Update the 'name' to 'Alice' and 'age' to 30 for the user with id 5. Write the SQL query.

UPDATE users SET name = 'Alice', age = 30 WHERE id = 5;

beginner

Can you update columns to NULL using UPDATE? How?

Yes, you can set a column to NULL by assigning NULL as the value, for example: SET column_name = NULL.

Which keyword is used to specify the columns to update in an SQL UPDATE statement?

A FROM

B SET

C WHERE

D SELECT

The SET keyword is used to list the columns and their new values in an UPDATE statement.

What happens if you omit the WHERE clause in an UPDATE statement?

A The query will fail

B No rows are updated

C Only one row is updated

D All rows in the table are updated

Without a WHERE clause, the UPDATE affects every row in the table.

How do you update two columns 'price' and 'stock' in one UPDATE statement?

A SET price = 10, stock = 5

B SET price = 10 AND stock = 5

C WHERE price = 10, stock = 5

D UPDATE price = 10, stock = 5

Multiple columns are updated by separating assignments with commas after SET.

Which of these is a valid UPDATE statement to change 'status' to 'active' and 'score' to 100 for user_id 7?

A UPDATE users SET status = 'active', score = 100 WHERE user_id = 7;

B UPDATE users SET status = 'active' AND score = 100 WHERE user_id = 7;

C UPDATE users WHERE user_id = 7 SET status = 'active', score = 100;

D UPDATE users SET status = 'active' score = 100 WHERE user_id = 7;

The correct syntax uses commas to separate column assignments after SET.

Can you update a column to NULL in SQL?

A Only if the column is a number

B No, NULL is not allowed in UPDATE

C Yes, by setting column = NULL

D Only if the column is a string

You can set any column to NULL by assigning NULL in the UPDATE statement.

Explain how to update multiple columns in a SQL table and why the WHERE clause is important.

Think about changing more than one detail at once and controlling which rows change.

Write an example SQL UPDATE statement that changes two columns for a specific row.

Use a simple table like users and columns like name and age.

Practice

1. What does the SQL UPDATE statement do when you specify multiple columns in the SET clause?

easy

A. It deletes the rows that match the condition.

B. It only updates the first column listed and ignores the rest.

C. It creates new rows with the specified column values.

D. It changes the values of all specified columns in the selected rows.

Solution

1. Step 1: Understand the UPDATE statement

   The UPDATE statement modifies existing rows in a table.

2. Step 2: Effect of multiple columns in SET clause

   When multiple columns are listed in SET, all those columns are updated in the rows that match the WHERE condition.

3. Final Answer:

   It changes the values of all specified columns in the selected rows. -> Option D

4. Quick Check:

   UPDATE with multiple columns updates all listed columns [OK]

Hint: SET updates all listed columns together [OK]

Common Mistakes:

• Thinking UPDATE adds new rows
• Believing UPDATE deletes rows
• Assuming only first column updates

2. Which of the following is the correct syntax to update two columns, price and stock, in a table named products?

easy

A. UPDATE products SET price = 10, stock = 50 WHERE id = 1;

B. UPDATE products (price = 10, stock = 50) WHERE id = 1;

C. UPDATE products SET (price, stock) = (10, 50) WHERE id = 1;

D. UPDATE products WHERE id = 1 SET price = 10, stock = 50;

Solution

1. Step 1: Recall correct UPDATE syntax

   The correct syntax is UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

2. Step 2: Check each option

   UPDATE products SET price = 10, stock = 50 WHERE id = 1; matches the correct syntax exactly. The other options have syntax errors.

3. Final Answer:

   UPDATE products SET price = 10, stock = 50 WHERE id = 1; -> Option A

4. Quick Check:

   SET column = value pairs separated by commas [OK]

Hint: Use SET column=value, column=value syntax [OK]

Common Mistakes:

• Using parentheses incorrectly in SET clause
• Placing WHERE before SET
• Missing commas between column assignments

3. Given the table employees with columns salary and bonus, what will be the result of this query?

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

medium

A. All employees get salary and bonus updated regardless of department.

B. Only the salary of Sales employees increases; bonus remains unchanged.

C. All employees in Sales get their salary increased by 1000 and bonus set to 500.

D. Syntax error because multiple columns cannot be updated together.

Solution

1. Step 1: Understand the WHERE clause effect

   The WHERE clause limits updates to rows where department = 'Sales'.

2. Step 2: Analyze SET clause changes

   Salary increases by 1000 and bonus is set to 500 for those rows only.

3. Final Answer:

   All employees in Sales get their salary increased by 1000 and bonus set to 500. -> Option C

4. Quick Check:

   WHERE filters rows; SET updates all listed columns [OK]

Hint: WHERE limits rows; SET updates all columns listed [OK]

Common Mistakes:

• Ignoring WHERE and updating all rows
• Thinking bonus stays unchanged
• Believing multiple columns update causes error

4. Identify the error in this SQL statement:

UPDATE orders SET status = 'Shipped' stock = 0 WHERE order_id = 123;

medium

A. WHERE clause should come before SET clause.

B. Missing comma between column assignments in SET clause.

C. Incorrect table name used.

D. Quotes around 'Shipped' should be double quotes.

Solution

1. Step 1: Check SET clause syntax

   Multiple columns must be separated by commas in SET clause.

2. Step 2: Identify missing comma

   Between status = 'Shipped' and stock = 0, a comma is missing.

3. Final Answer:

   Missing comma between column assignments in SET clause. -> Option B

4. Quick Check:

   Separate columns with commas in SET [OK]

Hint: Put commas between each column=value pair [OK]

Common Mistakes:

• Omitting commas between columns
• Placing WHERE before SET
• Using wrong quotes for strings

5. You want to update the price and discount columns in the products table only for items where category is 'Electronics' and current stock is less than 10. Which query correctly does this?

hard

A. UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' AND stock < 10;

B. UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' OR stock < 10;

C. UPDATE products SET price = price * 0.9 discount = 10 WHERE category = 'Electronics' AND stock < 10;

D. UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' AND stock > 10;

Solution

1. Step 1: Understand the condition requirements

   We want to update only rows where category is 'Electronics' AND stock is less than 10.

2. Step 2: Check each option's WHERE clause and syntax

   UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' AND stock < 10; uses AND with correct conditions and proper syntax. UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' OR stock < 10; uses OR, which updates more rows. UPDATE products SET price = price * 0.9 discount = 10 WHERE category = 'Electronics' AND stock < 10; misses a comma between assignments. UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' AND stock > 10; uses stock > 10, which is incorrect.

3. Final Answer:

   UPDATE products SET price = price * 0.9, discount = 10 WHERE category = 'Electronics' AND stock < 10; -> Option A

4. Quick Check:

   Use AND for combined conditions and commas between columns [OK]

Hint: Use AND for all conditions; separate columns with commas [OK]

Common Mistakes:

• Using OR instead of AND in WHERE
• Missing commas between columns
• Wrong comparison operators in WHERE