{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"update-and-delete-without-regret","title":"UPDATE and DELETE: rehearse it as a SELECT","url":"https://addaly.com/learn/data-and-sql/update-and-delete-without-regret","position":58,"of":66,"minutes":8,"answer":"Run the WHERE clause as a SELECT, read the rows and the count, then change the first word; for UPDATE FROM, also prove that no target row matches more than once.","keyIdea":"Run the WHERE clause as a SELECT, read the rows and the count, then change the first word; for UPDATE FROM, also prove that no target row matches more than once, because the database will pick one silently."},"body":{"format":"markdown","text":"## The statement that changes every row\n\n```sql\nUPDATE orders SET status = 'cancelled';\n```\n\nThere is no WHERE clause. Every order in the table is now cancelled. The database reports `UPDATE 31940` and waits for the next command. It has done exactly what it was told, and what it was told was a typo: a WHERE clause on the next line that was never sent, or a query pasted without its last line.\n\nEvery person who works with databases has a version of this story, and the ones who only have one version learned the habit in this lesson.\n\n## Rehearse\n\nBefore any UPDATE or DELETE, run the same WHERE clause as a SELECT, and look at what comes back:\n\n```sql\nSELECT count(*), min(placed_at), max(placed_at)\nFROM orders\nWHERE status = 'pendng';\n-- 3 rows, all from last Tuesday\n```\n\nThree rows, last Tuesday. That is what you expected: a typo in the data from one bad import. Now, and only now, convert the SELECT into the write:\n\n```sql\nUPDATE orders SET status = 'pending' WHERE status = 'pendng';\n-- UPDATE 3\n```\n\nThe row count after the write must match the count from the rehearsal. If it says `UPDATE 40000`, stop, because something between the rehearsal and the write changed, and the transaction lesson next explains how to take it back.\n\n`RETURNING` shows what was changed, which is the rehearsal's other half:\n\n```sql\nDELETE FROM orders WHERE status = 'test' RETURNING id, customer_id, amount;\n```\n\nRead the returned rows. If one of them is a real customer, you know before the transaction is committed.\n\n## UPDATE with a join\n\nCorrecting one table from another is `UPDATE ... FROM`:\n\n```sql\nUPDATE orders o\nSET    city = c.city\nFROM   customers c\nWHERE  c.id = o.customer_id\n  AND  o.city IS NULL;\n```\n\nEach order with a missing city takes it from its customer. This has the join hazard the whole course has been about. If the FROM matches more than one row per order, Postgres updates the order with one of them and does not say which, and MySQL and SQL Server have their own versions of the same silence. The rehearsal for an UPDATE FROM is therefore a count of matches per target row:\n\n```sql\nSELECT o.id, count(*) FROM orders o JOIN customers c ON c.id = o.customer_id\nGROUP BY o.id HAVING count(*) > 1;\n```\n\nIt must return nothing. `DELETE ... USING` is the same shape for deletes, with the same rehearsal.\n\n## Deleting in batches\n\n`DELETE FROM events WHERE created_at < '2024-01-01'` on a table with 200 million old rows will run for an hour, hold locks the whole time, and generate a transaction log the size of the table. Delete in slices instead:\n\n```sql\nDELETE FROM events\nWHERE id IN (SELECT id FROM events WHERE created_at < '2024-01-01' LIMIT 10000);\n```\n\nin a loop until the count is zero. Each slice commits quickly, other users get a turn between slices, and a mistake discovered after the third slice has cost thirty thousand rows rather than two hundred million. The partitioning lesson in the performance module shows the version that costs nothing: dropping a whole partition of old rows in one metadata operation.\n\n## Tools that help, and one that does not\n\nDBeaver, which is free, can be set to ask for confirmation before running an UPDATE or DELETE without a WHERE (\"Confirm dangerous query execution\"). Turn that on. pgAdmin has no such check, but its Query Tool can run with Auto commit switched off, so a write waits for your COMMIT. `psql` has no such setting; it is the tool that trusts you, and it is the one usually connected to production. Some teams alias `psql` to open with `--set AUTOCOMMIT=off`, so that every write is inside a transaction until you say `COMMIT`, which is the subject of the next lesson and the strongest protection available.\n\n## The habit\n\nNever type UPDATE or DELETE first. Type SELECT, read the rows, then edit the first word. The count in the rehearsal and the count in the write are the same number, or the write does not get committed."},"check":{"question":"`UPDATE orders o SET city = c.city FROM customer_addresses c WHERE c.customer_id = o.customer_id` runs without error and reports the expected row count, but some orders now show a city the customer moved away from years ago. What happened?","options":[{"text":"UPDATE FROM applies rows in reverse chronological order, so the oldest address is always the last one written and wins.","correct":false,"tempting":"There is no ordering in an UPDATE FROM; which matching row is used is unspecified, not reversed."},{"text":"Customers have several addresses, so each order matched more than one row and the database used one of them without saying which.","correct":true},{"text":"The WHERE clause is missing an `AND o.city IS NULL`, so orders that already had the right city were overwritten with the customer's current one.","correct":false,"tempting":"Would explain orders changing, but not why some show an address the customer no longer has; the multiple-match problem does."},{"text":"`customer_addresses` was joined without an index, and the sequential scan returned rows in physical order, which favours older addresses.","correct":false,"tempting":"Scan order can influence which of several matches is used, but the fault is that several matches exist; an index would not make the choice correct."}]},"prev":{"slug":"insert-and-upsert","title":"INSERT, and the INSERT you can run twice","url":"https://addaly.com/learn/data-and-sql/insert-and-upsert"},"next":{"slug":"transactions-and-rollback","title":"Transactions: making a mistake reversible","url":"https://addaly.com/learn/data-and-sql/transactions-and-rollback"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'UPDATE and DELETE: rehearse it as a SELECT', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/update-and-delete-without-regret (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"UPDATE and DELETE: rehearse it as a SELECT","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"Run the WHERE clause as a SELECT, read the rows and the count, then change the first word; for UPDATE FROM, also prove that no target row matches more than once, because the database will pick one silently.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-statement-that-changes-every-row","runs":[{"text":"The statement that changes every row"}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE orders SET status = 'cancelled';"},{"t":"p","runs":[{"text":"There is no WHERE clause. Every order in the table is now cancelled. The database reports "},{"text":"UPDATE 31940","code":true},{"text":" and waits for the next command. It has done exactly what it was told, and what it was told was a typo: a WHERE clause on the next line that was never sent, or a query pasted without its last line."}]},{"t":"p","runs":[{"text":"Every person who works with databases has a version of this story, and the ones who only have one version learned the habit in this lesson."}]},{"t":"h2","id":"rehearse","runs":[{"text":"Rehearse"}]},{"t":"p","runs":[{"text":"Before any UPDATE or DELETE, run the same WHERE clause as a SELECT, and look at what comes back:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*), min(placed_at), max(placed_at)\nFROM orders\nWHERE status = 'pendng';\n-- 3 rows, all from last Tuesday"},{"t":"p","runs":[{"text":"Three rows, last Tuesday. That is what you expected: a typo in the data from one bad import. Now, and only now, convert the SELECT into the write:"}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE orders SET status = 'pending' WHERE status = 'pendng';\n-- UPDATE 3"},{"t":"p","runs":[{"text":"The row count after the write must match the count from the rehearsal. If it says "},{"text":"UPDATE 40000","code":true},{"text":", stop, because something between the rehearsal and the write changed, and the transaction lesson next explains how to take it back."}]},{"t":"p","runs":[{"text":"RETURNING","code":true},{"text":" shows what was changed, which is the rehearsal’s other half:"}]},{"t":"code","lang":"sql","label":"SQL","text":"DELETE FROM orders WHERE status = 'test' RETURNING id, customer_id, amount;"},{"t":"p","runs":[{"text":"Read the returned rows. If one of them is a real customer, you know before the transaction is committed."}]},{"t":"h2","id":"update-with-a-join","runs":[{"text":"UPDATE with a join"}]},{"t":"p","runs":[{"text":"Correcting one table from another is "},{"text":"UPDATE ... FROM","code":true},{"text":":"}]},{"t":"code","lang":"sql","label":"SQL","text":"UPDATE orders o\nSET    city = c.city\nFROM   customers c\nWHERE  c.id = o.customer_id\n  AND  o.city IS NULL;"},{"t":"p","runs":[{"text":"Each order with a missing city takes it from its customer. This has the join hazard the whole course has been about. If the FROM matches more than one row per order, Postgres updates the order with one of them and does not say which, and MySQL and SQL Server have their own versions of the same silence. The rehearsal for an UPDATE FROM is therefore a count of matches per target row:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.id, count(*) FROM orders o JOIN customers c ON c.id = o.customer_id\nGROUP BY o.id HAVING count(*) > 1;"},{"t":"p","runs":[{"text":"It must return nothing. "},{"text":"DELETE ... USING","code":true},{"text":" is the same shape for deletes, with the same rehearsal."}]},{"t":"h2","id":"deleting-in-batches","runs":[{"text":"Deleting in batches"}]},{"t":"p","runs":[{"text":"DELETE FROM events WHERE created_at < '2024-01-01'","code":true},{"text":" on a table with 200 million old rows will run for an hour, hold locks the whole time, and generate a transaction log the size of the table. Delete in slices instead:"}]},{"t":"code","lang":"sql","label":"SQL","text":"DELETE FROM events\nWHERE id IN (SELECT id FROM events WHERE created_at < '2024-01-01' LIMIT 10000);"},{"t":"p","runs":[{"text":"in a loop until the count is zero. Each slice commits quickly, other users get a turn between slices, and a mistake discovered after the third slice has cost thirty thousand rows rather than two hundred million. The partitioning lesson in the performance module shows the version that costs nothing: dropping a whole partition of old rows in one metadata operation."}]},{"t":"h2","id":"tools-that-help-and-one-that-does-not","runs":[{"text":"Tools that help, and one that does not"}]},{"t":"p","runs":[{"text":"DBeaver, which is free, can be set to ask for confirmation before running an UPDATE or DELETE without a WHERE (“Confirm dangerous query execution”). Turn that on. pgAdmin has no such check, but its Query Tool can run with Auto commit switched off, so a write waits for your COMMIT. "},{"text":"psql","code":true},{"text":" has no such setting; it is the tool that trusts you, and it is the one usually connected to production. Some teams alias "},{"text":"psql","code":true},{"text":" to open with "},{"text":"--set AUTOCOMMIT=off","code":true},{"text":", so that every write is inside a transaction until you say "},{"text":"COMMIT","code":true},{"text":", which is the subject of the next lesson and the strongest protection available."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Never type UPDATE or DELETE first. Type SELECT, read the rows, then edit the first word. The count in the rehearsal and the count in the write are the same number, or the write does not get committed."}]}],"check":{"question":[{"text":"UPDATE orders o SET city = c.city FROM customer_addresses c WHERE c.customer_id = o.customer_id","code":true},{"text":" runs without error and reports the expected row count, but some orders now show a city the customer moved away from years ago. What happened?"}],"options":[{"text":[{"text":"UPDATE FROM applies rows in reverse chronological order, so the oldest address is always the last one written and wins."}],"correct":false,"tempting":[{"text":"There is no ordering in an UPDATE FROM; which matching row is used is unspecified, not reversed."}]},{"text":[{"text":"Customers have several addresses, so each order matched more than one row and the database used one of them without saying which."}],"correct":true,"tempting":null},{"text":[{"text":"The WHERE clause is missing an "},{"text":"AND o.city IS NULL","code":true},{"text":", so orders that already had the right city were overwritten with the customer’s current one."}],"correct":false,"tempting":[{"text":"Would explain orders changing, but not why some show an address the customer no longer has; the multiple-match problem does."}]},{"text":[{"text":"customer_addresses","code":true},{"text":" was joined without an index, and the sequential scan returned rows in physical order, which favours older addresses."}],"correct":false,"tempting":[{"text":"Scan order can influence which of several matches is used, but the fault is that several matches exist; an index would not make the choice correct."}]}],"order":[2,0,1,3],"why":null},"prev":{"slug":"insert-and-upsert","title":"INSERT, and the INSERT you can run twice","minutes":8},"next":{"slug":"transactions-and-rollback","title":"Transactions: making a mistake reversible","minutes":9}}}