Updating and Deleting Data

Updating Rows

UPDATE customers SET city = 'Bengaluru' WHERE id = 1;
UPDATE customers SET city = 'Bengaluru', updated = now() WHERE name = 'Priya Sharma';

Always include a WHERE clause, or every row in the table will be changed. The WHERE decides which rows the update touches.

Deleting Rows

DELETE FROM customers WHERE id = 3;
DELETE FROM customers WHERE city = 'Old City';

Without a WHERE clause, DELETE removes every row from the table. If you want to delete rows but reset the auto-increment counter, use TRUNCATE TABLE instead.

Verifying Changes

SELECT COUNT(*) FROM customers;
SELECT * FROM customers ORDER BY id;

Safe Updating Habits

• Run a SELECT with the same WHERE first to preview affected rows.
• Wrap risky edits in a transaction: START TRANSACTION; ... ROLLBACK; if wrong.
• Back up the table before mass UPDATE or DELETE.

START TRANSACTION;
DELETE FROM customers WHERE city = 'Test';   
SELECT COUNT(*) FROM customers;               
ROLLBACK;                                      -- undo the delete

Key Points

• UPDATE changes existing rows; DELETE removes them.
• The WHERE clause controls which rows are affected.
• Transactions let you undo mistakes with ROLLBACK.
• TRUNCATE removes all rows and resets auto-increment.