Skip to content
Back to DatabasesAll lessons

Databases

UPDATE & DELETE (changing and removing rows)

O(n) to scan for matching rows. This is O(1) average with an index on the WHERE column (see the Indexes lesson). O(1) extra space either way, though this in-memory version builds a fresh array.

The idea, in plain English

UPDATE is like using an eraser and pencil on one line of a paper ledger. You find the entry and change a field, without retyping the whole page. DELETE is tearing that line out of the ledger entirely. Both rely on WHERE to say exactly which row or rows to touch. Leave off the WHERE, and you would erase or tear out every row in the table.

How it works

  1. 1UPDATE table SET column = newValue WHERE condition finds every row that matches the condition. It changes only the listed column or columns on those rows, and leaves every other row untouched.
  2. 2DELETE FROM table WHERE condition finds every row that matches the condition and removes it from the table entirely. Rows that do not match stay exactly as they were.
  3. 3Both are permanent the instant they run, or the instant you COMMIT if you are inside a transaction (see that lesson). There is no automatic undo button.

When you'd use it

Use UPDATE whenever a fact changes, such as a user's email or a product's price. Use DELETE whenever a row should stop existing, such as a cancelled order or a discontinued product.

Common beginner mistakes

  • Do not run UPDATE or DELETE with no WHERE clause. That touches every row in the table, not just the one you meant.
  • Do not assume DELETE can be undone. Outside a transaction that has not committed yet, it cannot be undone. Run the equivalent SELECT first, to check exactly which rows WHERE will match.

Try it — edit and run

Click the code to edit · press ⌘/Ctrl+↵ to run

Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.

Expected output — hit Run to try it
Before:
1 Mug 8
2 Pen 2
3 Lamp 25
4 Chair 40
SQL: UPDATE products SET price = 10 WHERE id = 1
After UPDATE:
1 Mug 10
2 Pen 2
3 Lamp 25
4 Chair 40
SQL: DELETE FROM products WHERE id = 2
After DELETE:
1 Mug 10
3 Lamp 25
4 Chair 40

Not sure this is the right topic? See the learning paths → or where this leads →