UPDATE multiple columns in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after updating multiple columns?

Given the table Employees with columns id, name, salary, and department, what will be the result of this query?

UPDATE Employees SET salary = salary + 500, department = 'HR' WHERE id = 3;

Assuming the initial row with id = 3 is {id: 3, name: 'Alice', salary: 3000, department: 'Sales'}, what will be the updated row?

SQL

UPDATE Employees SET salary = salary + 500, department = 'HR' WHERE id = 3;

A {id: 3, name: 'Alice', salary: 3000, department: 'Sales'}

B {id: 3, name: 'Alice', salary: 3500, department: 'HR'}

C {id: 3, name: 'Alice', salary: 3500, department: 'Sales'}

D {id: 3, name: 'Alice', salary: 3000, department: 'HR'}

💡 Hint

Remember that both columns are updated in the same query for the rows matching the condition.

The query increases salary by 500 and changes department to 'HR' for the employee with id 3. So salary becomes 3000 + 500 = 3500 and department changes from 'Sales' to 'HR'.

🧠 Conceptual

intermediate

Which SQL statement correctly updates multiple columns?

You want to update the price and stock columns in a Products table for the product with product_id = 10. Which of the following SQL statements is correct?

A UPDATE Products SET price = 20, stock = 50 WHERE product_id = 10;

B UPDATE Products SET (price, stock) = (20, 50) WHERE product_id = 10;

C UPDATE Products WHERE product_id = 10 SET price = 20, stock = 50;

D UPDATE Products SET price = 20 AND stock = 50 WHERE product_id = 10;

💡 Hint

The SET clause lists columns and their new values separated by commas.

Option A uses the correct syntax: UPDATE table SET column1 = value1, column2 = value2 WHERE condition. Other options have syntax errors.

🔧 Debug

advanced

Identify the error in this UPDATE statement for multiple columns

Consider this SQL statement:

UPDATE Orders SET status = 'Shipped' stock = stock - 1 WHERE order_id = 100;

What error will this query produce?

A Syntax error due to missing WHERE clause

B Runtime error because stock cannot be decremented

C No error, query runs successfully

D Syntax error due to missing comma between column assignments

💡 Hint

Check the syntax between multiple column assignments in the SET clause.

Multiple columns in SET must be separated by commas. Missing comma causes syntax error.

❓ optimization

advanced

Optimizing multiple column updates in a large table

You need to update the status and last_updated columns for 10,000 rows in a large Tasks table. Which approach is generally better for performance?

A Delete the 10,000 rows and re-insert them with updated values

B Update each row individually in a loop with separate UPDATE statements

C Run a single UPDATE statement that sets both columns at once with a WHERE condition matching the 10,000 rows

D Run two separate UPDATE statements, one for each column, each with the same WHERE condition

💡 Hint

Minimize the number of UPDATE statements to reduce overhead.

A single UPDATE statement that changes multiple columns is more efficient than multiple separate updates or row-by-row updates.

❓ query_result

expert

What is the final state after a complex multiple column UPDATE?

Given the table Inventory with columns item_id, quantity, price, and discount, and initial row:

{item_id: 5, quantity: 100, price: 10, discount: 0}

What will be the row after running this query?

UPDATE Inventory SET quantity = quantity - 20, price = price * 0.9, discount = discount + 5 WHERE item_id = 5;

SQL

UPDATE Inventory SET quantity = quantity - 20, price = price * 0.9, discount = discount + 5 WHERE item_id = 5;

A {item_id: 5, quantity: 80, price: 9, discount: 5}

B {item_id: 5, quantity: 80, price: 10, discount: 5}

C {item_id: 5, quantity: 100, price: 9, discount: 5}

D {item_id: 5, quantity: 80, price: 9, discount: 0}

💡 Hint

Calculate each column update step by step.

Quantity decreases by 20 (100 - 20 = 80), price is multiplied by 0.9 (10 * 0.9 = 9), discount increases by 5 (0 + 5 = 5).