SQL INSERT, UPDATE, DELETE: Changing Data Safely

Everything on this site so far has read data. This page is about the other half of SQL: putting rows in, changing them, and taking them out. Three statements — INSERT, UPDATE, DELETE — and one habit that separates people who have lost production data from people who have not. The playground is the ideal place to practise these, because its database lives only in your browser tab: reload the page and the original data is back, no harm done.

INSERT — adding rows

INSERT INTO Products (product_id, name, category, unit_price, in_stock)
VALUES (209, 'Drift Mouse', 'Accessories', 45.50, 300);

Name the columns, then supply values in the same order. You can omit the column list and rely on the table's own column order — it is shorter, and it is also how INSERTs silently break the day someone adds a column to the table. Writing the list makes the statement self-documenting and future-proof, which is why it is the professional default.

Prove it worked with an ordinary SELECT:

SELECT * FROM Products WHERE product_id = 209;

UPDATE — changing rows that already exist

UPDATE Products
SET unit_price = 39.00
WHERE product_id = 209;

SET says what changes; WHERE says which rows it happens to. The value can be computed from the row's own current values, which is how stock adjustments and price increases are really written:

UPDATE Products
SET in_stock = in_stock - 10
WHERE product_id = 209;

An UPDATE with no WHERE updates every row in the table. No confirmation, no warning — the database does exactly what you asked. This is not a rare mistake; it is the classic one.

DELETE — removing rows

DELETE FROM Products
WHERE product_id = 209;

Same shape, same rule: the WHERE decides which rows die, and a DELETE without a WHERE empties the whole table. There is no undo in SQL itself — recovery means restoring a backup or, in a real system, rolling back the surrounding transaction.

The habit that saves careers: SELECT first

Before running an UPDATE or DELETE, run a SELECT with the exact same WHERE clause and look at what comes back:

SELECT product_id, name, unit_price
FROM Products
WHERE category = 'Accessories' AND unit_price < 50;

Those are the rows your UPDATE or DELETE will touch — count them, read them, and only then swap the SELECT line for the destructive statement. Thirty seconds of checking against a mistake that can take a day to undo. Every experienced database person does this, which tells you how they got to be experienced.

Where transactions fit

Production systems wrap risky changes in a transaction: BEGIN, run the statements, then COMMIT to keep the changes or ROLLBACK to undo them all. Either everything lands or nothing does — that all-or-nothing guarantee is the A in the ACID properties databases advertise. The browser engine here executes statements one at a time without transaction support, so practise the concept's shape now and expect BEGIN/COMMIT/ROLLBACK to be waiting for you in PostgreSQL, MySQL and every other server database.

DELETE vs TRUNCATE vs DROP

Three levels of destruction, regularly confused in interviews: DELETE removes rows (some or all, depending on WHERE) and can be rolled back in a transaction. TRUNCATE removes every row at once, faster, typically without firing per-row triggers. DROP removes the table itself — data, structure, everything. If the question is "how do I remove some rows", the answer is always DELETE with a WHERE.

Frequently asked questions

Related reading

• How to Write SQL Queries: A Complete Beginner's Guide
• The SQL WHERE Clause: Filtering Data with Every Operator
• 25 SQL Interview Questions and Answers