UPDATE & DELETE

UPDATE

UPDATE table_name
SET col1 = val1, col2 = val2
WHERE condition;

UPDATE students SET cgpa = 3.75 WHERE roll_no = 'CSE-101';

UPDATE students
SET department = 'SWE', updated_at = NOW()
WHERE department = 'CSE' AND admitted_on >= '2024-01-01';

-- expressions referencing the current value
UPDATE students SET cgpa = cgpa + 0.10 WHERE cgpa < 3.0;

-- conditional update
UPDATE students
SET grade = CASE
  WHEN cgpa >= 3.75 THEN 'A+'
  WHEN cgpa >= 3.50 THEN 'A'
  WHEN cgpa >= 3.00 THEN 'B'
  ELSE 'C'
END;

-- ordered, limited update
UPDATE students SET scholarship = 1 ORDER BY cgpa DESC LIMIT 10;

DELETE

DELETE FROM students WHERE roll_no = 'CSE-104';
DELETE FROM students WHERE cgpa < 2.0 AND admitted_on < '2020-01-01';
DELETE FROM students ORDER BY created_at ASC LIMIT 100;   -- batch cleanup
DELETE FROM students;                                     -- every row (slow)

Multi-table operations

-- update using a join
UPDATE students s
JOIN departments d ON s.department = d.code
SET s.dept_name = d.name_en
WHERE d.active = 1;

-- delete orphans
DELETE s FROM students s
LEFT JOIN departments d ON s.department = d.code
WHERE d.code IS NULL;

Safety practices

1. Preview first. Run the same WHERE as a SELECT before you change anything.
2. Wrap in a transaction so you can undo:

START TRANSACTION;
UPDATE students SET cgpa = 0 WHERE department = 'CSE';
SELECT COUNT(*) FROM students WHERE cgpa = 0;   -- check
ROLLBACK;   -- or COMMIT;

3. Safe update mode blocks UPDATE/DELETE without a key in the WHERE clause — it is on by default in Workbench:

SET SQL_SAFE_UPDATES = 1;

4. Check how many rows were affected with ROW_COUNT() right after the statement.

The single most common production accident is an UPDATE or DELETE where the WHERE clause was forgotten or matched more rows than expected. The habits above prevent nearly all of them.