Why UPDATE needs caution in SQL - See It in Action

Why UPDATE Needs Caution in SQL

📖 Scenario: You are managing a small library database. You want to update book information carefully to avoid mistakes that could change many records unintentionally.

🎯 Goal: Learn how to safely use the UPDATE statement in SQL by practicing with a sample books table. Understand how to limit updates to specific rows.

📋 What You'll Learn

Create a books table with specific columns and data

Add a condition variable to control which rows to update

Write an UPDATE query that changes only targeted rows

Add a final safety check to prevent accidental updates

💡 Why This Matters

🌍 Real World

Updating records carefully is important in real databases to avoid data loss or corruption.

💼 Career

Database administrators and developers must write safe UPDATE queries to maintain data integrity.

1

Create the books table with sample data

Create a table called books with columns id (integer), title (text), and author (text). Insert these exact rows: (1, 'The Hobbit', 'Tolkien'), (2, '1984', 'Orwell'), (3, 'Dune', 'Herbert').

SQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set a condition variable for the update

Create a variable called target_author and set it to the text 'Orwell'. This will help us update only books by this author.

SQL

CREATE TABLE books (id INTEGER, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'Tolkien'),
(2, '1984', 'Orwell'),
(3, 'Dune', 'Herbert');
-- Define the target_author variable
-- Your code here

Hint

Use \set to create a variable in SQL clients like psql.

3

Write an UPDATE query with a WHERE clause

Write an UPDATE statement to change the title to 'Nineteen Eighty-Four' only for books where the author matches the variable target_author.

SQL

CREATE TABLE books (id INTEGER, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'Tolkien'),
(2, '1984', 'Orwell'),
(3, 'Dune', 'Herbert');

-- Variable for update condition
\set target_author 'Orwell'

-- Write UPDATE query here
-- Your code here

Hint

Use WHERE author = :'target_author' to limit the update to the right rows.

4

Add a safety check to prevent accidental updates

Add a LIMIT 1 clause to the UPDATE statement to ensure only one row is updated at a time.

SQL

CREATE TABLE books (id INTEGER, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'Tolkien'),
(2, '1984', 'Orwell'),
(3, 'Dune', 'Herbert');

-- Variable for update condition
\set target_author 'Orwell'

UPDATE books SET title = 'Nineteen Eighty-Four' WHERE author = :'target_author';
-- Add LIMIT 1 to the UPDATE
-- Your code here

Hint

Adding LIMIT 1 helps avoid updating too many rows by mistake.

Practice

1. Why should you be careful when using the UPDATE statement without a WHERE clause?

easy

A. Because it will only update the first row.

B. Because it will delete the table.

C. Because it will create a new table.

D. Because it will update all rows in the table, possibly causing unwanted changes.

Solution

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

   The WHERE clause limits which rows get updated. Without it, all rows are affected.

2. Step 2: Consider the effect of missing WHERE

   Without WHERE, every row in the table changes, which can cause data loss or errors.

3. Final Answer:

   Because it will update all rows in the table, possibly causing unwanted changes. -> Option D

4. Quick Check:

   Missing WHERE updates all rows [OK]

Hint: Always use WHERE to limit rows in UPDATE [OK]

Common Mistakes:

• Assuming UPDATE only changes one row by default
• Forgetting WHERE clause causes full table update
• Thinking UPDATE deletes data

2. Which of the following is the correct syntax to update the salary to 5000 for employees in department 10?

easy

A. UPDATE employees SET salary = 5000 department = 10;

B. UPDATE employees SET salary = 5000 WHERE department = 10;

C. UPDATE employees SET salary 5000 WHERE department = 10;

D. UPDATE employees salary = 5000 WHERE department = 10;

Solution

1. Step 1: Recall correct UPDATE syntax

   The correct syntax is UPDATE table SET column = value WHERE condition;.

2. Step 2: Check each option

   UPDATE employees SET salary = 5000 WHERE department = 10; matches the correct syntax exactly. Others miss keywords or punctuation.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE department = 10; -> Option B

4. Quick Check:

   Correct UPDATE syntax uses SET and WHERE [OK]

Hint: UPDATE needs SET and WHERE for targeted changes [OK]

Common Mistakes:

• Omitting SET keyword
• Missing equal sign (=)
• Leaving out WHERE clause

3. Given the table products with rows:
{id:1, price:100}, {id:2, price:200}, {id:3, price:300}
What will be the result after running:
UPDATE products SET price = price + 50 WHERE id > 1;
Then selecting all rows?

medium

A. [{id:1, price:150}, {id:2, price:250}, {id:3, price:350}]

B. [{id:1, price:150}, {id:2, price:200}, {id:3, price:300}]

C. [{id:1, price:100}, {id:2, price:250}, {id:3, price:350}]

D. [{id:1, price:100}, {id:2, price:200}, {id:3, price:300}]

Solution

1. Step 1: Identify rows affected by WHERE clause

   The condition id > 1 affects rows with id 2 and 3 only.

2. Step 2: Calculate new prices for affected rows

   Price for id 2: 200 + 50 = 250; Price for id 3: 300 + 50 = 350; id 1 remains 100.

3. Final Answer:

   [{id:1, price:100}, {id:2, price:250}, {id:3, price:350}] -> Option C

4. Quick Check:

   Only rows with id > 1 updated [OK]

Hint: Check WHERE condition carefully to know which rows update [OK]

Common Mistakes:

• Updating all rows ignoring WHERE
• Updating wrong rows by misreading condition
• Changing values incorrectly

4. What is wrong with this SQL statement?
UPDATE employees SET salary = salary + 1000 WHERE;

medium

A. The WHERE clause is incomplete and causes a syntax error.

B. The SET keyword is missing.

C. The table name is incorrect.

D. The salary column cannot be updated.

Solution

1. Step 1: Check the WHERE clause syntax

   The WHERE clause must have a condition after it. Here it is empty, causing error.

2. Step 2: Verify other parts of the statement

   SET keyword and table name are correct; salary can be updated.

3. Final Answer:

   The WHERE clause is incomplete and causes a syntax error. -> Option A

4. Quick Check:

   Incomplete WHERE causes syntax error [OK]

Hint: Always provide a condition after WHERE [OK]

Common Mistakes:

• Leaving WHERE empty
• Forgetting condition after WHERE
• Assuming WHERE is optional but incomplete

5. You want to increase the price by 10% only for products priced below 100. Which UPDATE statement is safest to avoid changing other rows?

hard

A. UPDATE products SET price = price * 1.10 WHERE price < 100;

B. UPDATE products SET price = price * 1.10;

C. UPDATE products SET price = price + 10 WHERE price < 100;

D. UPDATE products SET price = price * 1.10 WHERE price > 100;

Solution

1. Step 1: Identify the condition to limit rows

   Only products with price less than 100 should be updated, so WHERE price < 100 is needed.

2. Step 2: Choose correct price increase method

   Increasing by 10% means multiplying price by 1.10, not adding 10.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE price < 100; -> Option A

4. Quick Check:

   Use WHERE and correct math for safe updates [OK]

Hint: Use WHERE to limit rows and correct math for updates [OK]

Common Mistakes:

• Forgetting WHERE and updating all rows
• Adding fixed amount instead of percentage
• Using wrong condition in WHERE