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. One thing to know about the playground: it starts from the original data every time it opens, so a statement that depends on an earlier one has to run in the same tab, after it. The blocks below therefore repeat the INSERT they rely on:

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

SELECT * FROM Products WHERE product_id = 209;

UPDATE — changing rows that already exist

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

UPDATE Products
SET unit_price = 39.00
WHERE product_id = 209;

SELECT * FROM Products 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.

Sometimes you do not know whether the row is there yet: a nightly price feed, say, that includes products you already sell and some you do not. A plain INSERT is refused as soon as it meets a product that already exists, and a plain UPDATE silently skips the new ones. An upsert does both in one statement. SQLite and PostgreSQL spell it ON CONFLICT ... DO UPDATE, where excluded means the row you tried to insert:

INSERT INTO Products (product_id, name, category, unit_price, in_stock)
VALUES (201, 'Aurora Laptop 14"', 'Hardware',    1249,  40),
       (209, 'Drift Mouse',       'Accessories', 45.50, 300)
ON CONFLICT (product_id) DO UPDATE
  SET unit_price = excluded.unit_price,
      in_stock   = excluded.in_stock;

SELECT * FROM Products WHERE product_id IN (201, 209) ORDER BY product_id;

Product 201 already existed at 1299 with 42 in stock, so its price and stock were updated in place; 209 did not exist, so it was inserted. MySQL has its own spelling, and SQL Server uses MERGE, which the dialect differences guide shows in full:

-- MySQL
INSERT INTO Products (product_id, name, category, unit_price, in_stock)
VALUES (201, 'Aurora Laptop 14"', 'Hardware',    1249,  40),
       (209, 'Drift Mouse',       'Accessories', 45.50, 300)
ON DUPLICATE KEY UPDATE
  unit_price = VALUES(unit_price),
  in_stock   = VALUES(in_stock);

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, if the DELETE ran inside a transaction you opened first, rolling that transaction back, which the playground lets you try below.

SQLite and PostgreSQL can also hand back the rows a statement changed, if you add RETURNING. On a DELETE that is a record of exactly what was removed:

DELETE FROM Orders
WHERE status = 'Cancelled'
RETURNING order_id, status, order_total;

Both cancelled orders are listed as they were at the moment they were deleted. RETURNING works on INSERT and UPDATE too, where it is a common way to learn the id a new row was given. SQL Server’s version is the OUTPUT clause; MySQL has no equivalent.

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 < 200;

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. SQLite, the engine the playground runs, supports transactions, so you can watch a DELETE being taken back:

BEGIN TRANSACTION;
DELETE FROM Products WHERE category = 'Accessories';
ROLLBACK;

SELECT name, category FROM Products WHERE category = 'Accessories';

Both accessories are still there: the DELETE removed them, and the ROLLBACK put them back. Replace ROLLBACK with COMMIT and run it again, and the query comes back empty. On data that matters the habit is to open the transaction, make the change, check it with a SELECT, and only then commit.

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. SQLite, and so the playground, has no TRUNCATE at all: a DELETE with no WHERE is its equivalent.

Frequently asked questions

Related reading

• DELETE vs TRUNCATE vs DROP in SQL: The Real Difference
• SQL Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT and Keys
• 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