Reference

Safe SQL UPDATE & DELETE

Preview rows, check dependencies, and use transactions so you never change more rows than you meant to.

UPDATE and DELETE change data permanently. The most common and most painful mistake is forgetting the WHEREclause, which silently affects every row in the table.

The checklist

Write the WHERE clause as a SELECT first.
Confirm the row count is what you expect.
Eyeball a few of the matched rows.
Run the change inside a transaction when possible.
Re-check the affected row count.
COMMIT only once you're satisfied. Otherwise ROLLBACK.

Preview first

Step 1: SELECT what you'll change

select *
from   orders
where  status = 'cancelled'
  and  order_date < date '2023-01-01'

Then change exactly those rows

Same WHERE, now as DELETE

delete from orders
where  status = 'cancelled'
  and  order_date < date '2023-01-01'

Wrap risky changes in a transaction

Reversible until COMMIT

begin;

update products
set    price = price * 1.10
where  category_id = 2;

-- check the result, then keep or undo:
-- commit;
-- rollback;

SQL Server: check dependencies before DELETE

In SQL Server, a safe delete usually means checking child tables before you remove parent rows. Foreign keys may block the delete, or cascading rules may delete more data than you expected.

Find foreign keys that reference a table

select fk.name as foreign_key_name,
       object_name(fk.parent_object_id) as child_table,
       col_name(fkc.parent_object_id, fkc.parent_column_id) as child_column,
       object_name(fk.referenced_object_id) as parent_table,
       col_name(fkc.referenced_object_id, fkc.referenced_column_id) as parent_column,
       fk.delete_referential_action_desc
from sys.foreign_keys fk
join sys.foreign_key_columns fkc
  on fkc.constraint_object_id = fk.object_id
where fk.referenced_object_id = object_id('dbo.Customers');

Delete inside a transaction and capture deleted rows

begin transaction;

select *
from dbo.Customers
where CustomerId = 42;

delete from dbo.Customers
output deleted.*
where CustomerId = 42;

-- commit transaction;
-- rollback transaction;

Watch out

A bare UPDATE products SET price = 0; with noWHERE resets every product. If you only ever run destructive statements after previewing them with a matching SELECT, you'll avoid almost every accident.

Dialect note

In the SQLShed Playground, all data lives in a disposable in-browser database. Experiment freely, then reload to reset. Real databases give you no such undo button.

Lesson

What Is SQL DML?

Understand INSERT, UPDATE, and DELETE.

Lesson

Adding Rows with SQL INSERT

Add rows and confirm they landed.

Lesson

Updating Rows with SQL UPDATE

Change existing rows safely.

Lesson

Deleting Rows Safely with SQL DELETE

Preview rows before deleting them.