UPDATE without WHERE (danger) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after running this UPDATE?

Consider a table Employees with columns id, name, and salary. Initially, the table has these rows:

id | name    | salary
---+---------+--------
1  | Alice   | 5000
2  | Bob     | 6000
3  | Charlie | 7000

What will be the salaries of all employees after running this query?

UPDATE Employees SET salary = 10000;

SQL

UPDATE Employees SET salary = 10000;

A Only Alice's salary is 10000, others unchanged

B All employees have salary 10000

C Only Bob and Charlie have salary 10000, Alice unchanged

D No salaries changed

💡 Hint

Think about what happens if you don't specify a WHERE clause in an UPDATE statement.

Without a WHERE clause, the UPDATE affects all rows in the table. So every employee's salary is set to 10000.

🧠 Conceptual

intermediate

Why is running UPDATE without WHERE dangerous?

Which of the following best explains the danger of running an UPDATE statement without a WHERE clause?

A It updates all rows, possibly overwriting data unintentionally

B It causes a syntax error and the query fails

C It only updates the first row and ignores the rest

D It locks the table permanently

💡 Hint

Think about what happens when no filter is applied to an update.

Without a WHERE clause, the UPDATE affects every row, which can overwrite data unintentionally and cause data loss.

📝 Syntax

advanced

Which UPDATE statement will update only Bob's salary to 8000?

Given the Employees table, select the correct UPDATE statement that changes only Bob's salary to 8000.

A UPDATE Employees WHERE name = 'Bob' SET salary = 8000;

B UPDATE Employees SET salary = 8000;

C UPDATE Employees SET salary = 8000 WHERE id;

D UPDATE Employees SET salary = 8000 WHERE name = 'Bob';

💡 Hint

Remember the correct order of clauses in an UPDATE statement.

The correct syntax is UPDATE table SET column = value WHERE condition;. Option D follows this correctly and targets only Bob.

❓ query_result

advanced

What happens if you run UPDATE without WHERE on a table with 0 rows?

Consider an empty table Products with columns id and price. What is the result of running:

UPDATE Products SET price = 10;

What will be the state of the table after this query?

A The table remains empty with 0 rows

B All rows have price set to 10

C The table is deleted

D Syntax error occurs

💡 Hint

Think about how UPDATE affects rows when there are none.

If the table has no rows, UPDATE affects zero rows. The table remains empty.

🔧 Debug

expert

Why did this UPDATE without WHERE cause data loss?

A developer ran this query:

UPDATE Orders SET status = 'shipped';

They intended to update only orders with id > 100. What happened and why?

A The query failed due to missing WHERE clause

B Only orders with id > 100 were updated automatically

C All orders had their status changed to 'shipped' because no WHERE clause was used

D Only the first order was updated

💡 Hint

Recall what happens when UPDATE is run without a filter.

Without a WHERE clause, the UPDATE affects every row, causing unintended data changes and data loss.

Practice

1.

What happens if you run an UPDATE statement without a WHERE clause?

easy

A. All rows in the table will be updated.

B. Only the first row will be updated.

C. No rows will be updated.

D. The query will cause a syntax error.

Solution

1. Step 1: Understand the role of WHERE clause in UPDATE

   The WHERE clause limits which rows are updated. Without it, no filter is applied.

2. Step 2: Effect of missing WHERE clause

   Without WHERE, the update affects every row in the table.

3. Final Answer:

   All rows in the table will be updated. -> Option A

4. Quick Check:

   UPDATE without WHERE updates all rows [OK]

Hint: No WHERE means update every row in the table [OK]

Common Mistakes:

• Thinking only one row updates without WHERE
• Assuming syntax error occurs without WHERE
• Believing no rows are updated without WHERE

2.

Which of the following is the correct syntax to update the salary column to 5000 for all employees in the employees table?

UPDATE employees SET salary = 5000;

easy

A. UPDATE employees SET salary = 5000 WHERE employee_id = 1;

B. UPDATE SET salary = 5000 FROM employees;

C. UPDATE employees salary = 5000;

D. UPDATE employees SET salary = 5000;

Solution

1. Step 1: Identify correct UPDATE syntax

   The correct syntax is UPDATE table_name SET column = value; to update all rows.

2. Step 2: Check each option

   UPDATE employees SET salary = 5000; matches correct syntax without WHERE, updating all rows. Others have syntax errors or extra clauses.

3. Final Answer:

   UPDATE employees SET salary = 5000; -> Option D

4. Quick Check:

   UPDATE table SET column = value; updates all rows [OK]

Hint: UPDATE table SET column=value updates all rows without WHERE [OK]

Common Mistakes:

• Omitting SET keyword
• Using WHERE when not needed for all rows
• Incorrect order of clauses

3.

Given the table products with 3 rows:

id | name    | price
---+---------+-------
1  | Pen     | 10
2  | Pencil  | 5
3  | Eraser  | 3

What will be the result after running this query?

UPDATE products SET price = price + 2;

medium

A. Only the first product's price increases by 2.

B. All products' prices increase by 2.

C. No prices change because WHERE is missing.

D. Syntax error due to missing WHERE.

Solution

1. Step 1: Understand UPDATE without WHERE effect

   Without a WHERE clause, the update applies to all rows in the table.

2. Step 2: Apply the price update

   Each product's price increases by 2, so prices become 12, 7, and 5 respectively.

3. Final Answer:

   All products' prices increase by 2. -> Option B

4. Quick Check:

   UPDATE without WHERE updates all rows [OK]

Hint: No WHERE means all rows updated, so all prices change [OK]

Common Mistakes:

• Thinking only one row updates
• Assuming missing WHERE causes no update
• Expecting syntax error without WHERE

4.

Consider this query intended to update only employees in department 10:

UPDATE employees SET salary = salary + 1000;

Why might this be a problem?

medium

A. It will update salaries for all employees, not just department 10.

B. It will cause a syntax error because WHERE is missing.

C. It will update no rows because no condition is specified.

D. It will only update the first employee's salary.

Solution

1. Step 1: Identify missing WHERE clause

   The query lacks a WHERE clause to filter employees by department.

2. Step 2: Effect of missing WHERE

   Without filtering, the update applies to all employees, not just those in department 10.

3. Final Answer:

   It will update salaries for all employees, not just department 10. -> Option A

4. Quick Check:

   Missing WHERE updates all rows, causing unintended changes [OK]

Hint: Always use WHERE to limit UPDATE scope [OK]

Common Mistakes:

• Assuming syntax error without WHERE
• Thinking no rows update without WHERE
• Believing only one row updates by default

5.

You want to increase the stock by 10 only for products with stock less than 5. Which query correctly avoids the danger of updating all rows?

hard

A. UPDATE products SET stock = stock + 10;

B. UPDATE products stock = stock + 10 WHERE stock < 5;

C. UPDATE products SET stock = stock + 10 WHERE stock < 5;

D. UPDATE products SET stock = stock + 10 WHERE stock > 5;

Solution

1. Step 1: Identify the condition to limit update

   We want to update only rows where stock < 5, so a WHERE clause is needed.

2. Step 2: Check query correctness

   UPDATE products SET stock = stock + 10 WHERE stock < 5; uses correct syntax and condition to update only matching rows. UPDATE products SET stock = stock + 10; (no WHERE) updates all rows. UPDATE products stock = stock + 10 WHERE stock < 5; (missing SET) has syntax error. UPDATE products SET stock = stock + 10 WHERE stock > 5; updates wrong rows (opposite condition).

3. Final Answer:

   UPDATE products SET stock = stock + 10 WHERE stock < 5; -> Option C

4. Quick Check:

   Use WHERE to limit UPDATE to desired rows [OK]

Hint: Use WHERE to update only targeted rows [OK]

Common Mistakes:

• Omitting WHERE and updating all rows
• Using wrong comparison operator
• Syntax errors by missing SET keyword