Why UPDATE needs caution in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Effect of UPDATE without WHERE clause

Consider a table Employees with columns id, name, and salary. What will be the result after running this query?

UPDATE Employees SET salary = salary + 1000;

Assume the table initially has 3 rows with salaries 3000, 4000, and 5000.

SQL

UPDATE Employees SET salary = salary + 1000;

A No salaries will change because WHERE clause is missing.

B Only the first employee's salary will increase by 1000.

C All 3 employees will have their salary increased by 1000.

D The query will cause a syntax error.

💡 Hint

Think about what happens if you don't limit which rows to update.

Without a WHERE clause, UPDATE affects every row in the table. So all employees get their salary increased by 1000.

❓ query_result

intermediate

UPDATE with incorrect WHERE condition

Given a table Products with columns product_id, price, and category, what will be the result of this query?

UPDATE Products SET price = price * 0.9 WHERE category = 'Electronics' AND price > 1000;

Assume only one product has category 'Electronics' but its price is 900.

SQL

UPDATE Products SET price = price * 0.9 WHERE category = 'Electronics' AND price > 1000;

A All Electronics products will have their price reduced by 10%.

B The query will update all products regardless of category.

C Only products with price less than 1000 will be updated.

D No product prices will change because no product meets both conditions.

💡 Hint

Check if any product matches both conditions in WHERE.

The WHERE clause requires category 'Electronics' and price > 1000. Since no product meets both, no rows are updated.

📝 Syntax

advanced

Identify the syntax error in UPDATE statement

Which option contains a syntax error in the UPDATE statement?

A UPDATE Orders SET status = 'Shipped' WHERE order_id = 10;

B UPDATE Products price = 20 WHERE product_id = 3;

C UPDATE Employees SET salary = salary + 500 WHERE department = 'Sales';

D UPDATE Customers SET city = 'Paris' WHERE id = 5;

💡 Hint

Look for missing keywords or punctuation.

Option B is missing the keyword SET before the column assignment, causing a syntax error.

🔧 Debug

advanced

Unexpected data change after UPDATE

A developer runs this query:

UPDATE Inventory SET quantity = quantity - 1 WHERE product_id = 101;

But after running it, the quantity for all products decreased by 1. What is the most likely cause?

A The WHERE clause was missing or incorrect, so all rows were updated.

B The quantity column is not numeric, so subtraction failed.

C The product_id 101 does not exist, so no rows updated.

D The database automatically updates all rows when one is updated.

💡 Hint

Think about what happens if WHERE clause is ignored or wrong.

If WHERE is missing or wrong, UPDATE affects all rows, causing all quantities to decrease.

🧠 Conceptual

expert

Why must UPDATE be used with caution in multi-user environments?

In a busy database with many users, why is it important to be cautious when running UPDATE statements?

A Because UPDATE can lock rows or tables, causing delays or conflicts for other users.

B Because UPDATE statements always delete data permanently.

C Because UPDATE statements automatically create backups before changing data.

D Because UPDATE statements cannot be rolled back once executed.

💡 Hint

Consider how databases handle multiple users changing data at the same time.

UPDATE can lock data to keep it consistent, but this can block other users and cause delays or deadlocks.