Lesson 20 of 26 · see the practice database

Changing and removing rows: UPDATE and DELETE

UPDATE changes values in rows that already exist. DELETE removes rows. Both are simple to write, and both are dangerous for one reason: without a WHERE, they act on every row in the table. This lesson teaches the statements and, just as important, a routine for using them safely.

UPDATE

Name the table, say what to set, and say which rows.

UPDATE products
SET price = 70
WHERE id = 5;

SELECT id, name, price FROM products WHERE id = 5;

The notebook now costs 70 rupees. The WHERE picked the row by its primary key, which is the safest way to change exactly one row.

Changing several columns

Separate them with commas.

UPDATE customers
SET city = 'Mohali', email = 'meena@example.com'
WHERE id = 3;

SELECT id, name, city, email FROM customers WHERE id = 3;

Using the old value

The new value can be worked out from the current one. This raises every Sales salary by 10 percent, by adding a tenth of the salary to itself.

UPDATE employees
SET salary = salary + salary / 10
WHERE department = 'Sales';

SELECT name, department, salary FROM employees WHERE department = 'Sales';

The right-hand side always uses the values from before the update. Three rows changed, because three rows matched. An UPDATE changes every row that passes its WHERE, whether that is none, one or a million.

Taking stock off the shelf after a sale works the same way:

UPDATE products
SET stock = stock - 2
WHERE id = 1;

The disaster: no WHERE

UPDATE products
SET price = 70;

That is a valid statement. It sets the price of every product to 70. There is no warning, no "are you sure?", and in most systems no undo. People have lost real data to exactly this, usually by running a statement before they had finished typing it.

The safety routine

Before any UPDATE or DELETE on data that matters:

1. Write it as a SELECT first. Use the same WHERE and look at the rows that come back. Those are the rows you are about to change.
2. Check the count. If you meant to change one row and the SELECT shows forty, stop.
3. Then turn it into the UPDATE or DELETE, changing only the first line.
4. Read the "rows changed" figure the database reports afterwards.
5. On a real system, do it inside a transaction: BEGIN, run the change, check the result with a SELECT, then COMMIT if it is right or ROLLBACK if it is not.

Step 1 in practice. Suppose pending orders older than October should be cancelled. First look:

SELECT id, order_date, status
FROM orders
WHERE status = 'pending' AND order_date < '2026-10-01';

Two rows, as expected. Now the change, with the very same WHERE:

UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending' AND order_date < '2026-10-01';

Setting a value to NULL

Here, and only here, you write = NULL. In SET, the equals sign stores a value. It is not a comparison.

UPDATE customers
SET email = NULL
WHERE id = 12;

An UPDATE that uses another table

A subquery can pick the rows. This marks as sold out every product that has never been ordered.

UPDATE products
SET stock = 0
WHERE id NOT IN (SELECT product_id FROM order_items);

DELETE

Name the table and say which rows.

DELETE FROM orders
WHERE id = 9;

SELECT COUNT(*) AS orders_left FROM orders;

DELETE removes whole rows. There is no column list. To blank out one value in a row, you want UPDATE ... SET column = NULL, not DELETE.

Deleting by a condition removes every row that matches.

DELETE FROM order_items
WHERE order_id = 9;

That removed the lines of the order just deleted. Had we left them, they would point to an order that no longer exists. Such rows are called orphans, and they make totals wrong in ways that are hard to trace.

The disaster, again

DELETE FROM customers;

Every customer, gone. The table itself remains, empty. The same safety routine applies: SELECT first, check the count, then delete.

Foreign keys guard against orphans

A properly set up database refuses to delete a row that others point to. Our orders table declares that customer_id refers to customers. With that check switched on, deleting a customer who has orders is rejected with an error, and you must deal with the orders first.

SQLite ships with this check switched off unless a program turns it on, so in the practice box such a delete goes through. MySQL (with its usual InnoDB tables), PostgreSQL, SQL Server and Oracle enforce it always. A table can also be told to delete the dependent rows by itself, with ON DELETE CASCADE, which is convenient and deserves care.

Delete, or mark as deleted?

Many systems never really delete. They add a column such as active or deleted_at and set it with an UPDATE. The row stays for the records, reports can still count it, and a mistake is easy to reverse. Every query must then remember to filter on that column. For anything a business may need to look back on, such as customers, orders and invoices, marking is the usual choice.

Emptying a table quickly

To remove all rows of a large table on purpose, MySQL, PostgreSQL, SQL Server and Oracle offer TRUNCATE TABLE name. It is far quicker than DELETE because it does not handle rows one by one, and for the same reason it is harder or impossible to undo. SQLite has no TRUNCATE; a DELETE without WHERE does the job there.

Summary

Try it yourself

In the practice box, press Reset the database first so that you start from the original data.

UPDATE products SET price = 125 WHERE id = 9;

UPDATE employees SET salary = salary + 2000 WHERE department = 'Warehouse';

DELETE FROM orders WHERE status = 'cancelled';

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.