SQL INSERT, UPDATE and DELETE Explained Simply

Most SQL tutorials spend weeks on SELECT, and that makes sense, because reading data is what you’ll do most. But sooner or later you need to change data: add a new customer, fix a typo in a price, remove a test order. That’s the job of three commands: INSERT, UPDATE and DELETE.

They’re simple to learn. They’re also the commands that can do real damage if you’re careless, so along with the syntax I’ll share the habits that have saved me more than once.

Diagram showing how INSERT adds, UPDATE changes and DELETE removes table rows

The Sample Table

We’ll work with a small products table:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    price      DECIMAL(10,2) NOT NULL,
    stock      INT DEFAULT 0
);
product_idnamepricestock
1Notebook2.50120
2Pen0.80300
3Backpack18.0025

INSERT: Adding New Rows

INSERT INTO adds rows to a table. You list the columns, then the values in the same order:

INSERT INTO products (product_id, name, price, stock)
VALUES (4, 'Stapler', 6.75, 40);

The table now has four rows. A few things worth knowing:

  • Text and dates go in single quotes. Numbers don’t.
  • Always list the column names, even though SQL lets you skip them. If someone adds a column to the table later, an INSERT without column names will break or, worse, put values in the wrong columns.
  • If you leave a column out, it gets its default value (here stock would be 0) or NULL.

Inserting several rows at once

Most databases let you add many rows in one statement, which is faster than running one INSERT per row:

INSERT INTO products (product_id, name, price, stock)
VALUES
    (5, 'Ruler',  1.20, 80),
    (6, 'Eraser', 0.50, 150),
    (7, 'Marker', 1.90, 60);

Inserting from another table

You can also insert the result of a query. This is handy for copying or archiving data:

INSERT INTO products_archive (product_id, name, price)
SELECT product_id, name, price
FROM products
WHERE stock = 0;

Letting the database pick the id

In real systems you rarely type the id yourself. The column is set to generate it (AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, GENERATED ALWAYS AS IDENTITY in PostgreSQL), and you just leave it out of the insert. There’s more on that in Primary Keys vs. Foreign Keys.

UPDATE: Changing Existing Rows

UPDATE changes values in rows that already exist. You say which table, which columns get which new values, and which rows to touch:

UPDATE products
SET price = 2.75
WHERE product_id = 1;

Only the notebook changes. You can update several columns at once, and you can use the current value in the calculation:

-- 10% price increase and 50 more in stock for the backpack
UPDATE products
SET price = price * 1.10,
    stock = stock + 50
WHERE product_id = 3;

The backpack is now 19.80 with 75 in stock.

The mistake everyone makes once

Look at this:

UPDATE products
SET price = 0;

There’s no WHERE. SQL doesn’t ask “are you sure?”. It sets the price of every product to zero. I’ve seen this happen on a live system, and the restore took most of an afternoon. The WHERE clause is what limits the change, and it works exactly like it does in a SELECT (see WHERE, ORDER BY and LIMIT).

DELETE: Removing Rows

DELETE removes whole rows. Again, WHERE decides which ones:

DELETE FROM products
WHERE product_id = 2;

The pen is gone. And just like UPDATE, a DELETE without WHERE empties the whole table:

DELETE FROM products;   -- removes every row!

DELETE vs. TRUNCATE vs. DROP

These three get mixed up a lot:

CommandWhat it removesCan use WHERE?Notes
DELETESome or all rowsYesRow by row, can be rolled back, fires triggers
TRUNCATE TABLEAll rowsNoMuch faster on big tables, resets identity counters in most databases
DROP TABLEThe rows and the table itselfNoThe table no longer exists afterwards

Deleting rows that other tables depend on

If an orders table has a foreign key pointing at products, the database will usually refuse to delete a product that still has orders. That’s a good thing. It stops you creating orphan rows. Many systems don’t delete at all and use a “soft delete” instead, like an is_active column set to 0, so history is kept.

Insert or Update? (Upsert)

A very common job: “add this product, or update it if it’s already there”. Use INSERT for rows that don’t exist yet and UPDATE for rows that do. When you don’t know which, you need an upsert. Every database has its own syntax:

-- MySQL / MariaDB
INSERT INTO products (product_id, name, price, stock)
VALUES (2, 'Pen', 0.85, 50)
ON DUPLICATE KEY UPDATE price = VALUES(price),
                        stock = stock + VALUES(stock);

-- PostgreSQL
INSERT INTO products (product_id, name, price, stock)
VALUES (2, 'Pen', 0.85, 50)
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price,
              stock = products.stock + EXCLUDED.stock;

-- SQL Server: update first, insert if nothing was updated
BEGIN TRANSACTION;

UPDATE products WITH (UPDLOCK, SERIALIZABLE)
SET price = 0.85, stock = stock + 50
WHERE product_id = 2;

IF @@ROWCOUNT = 0
    INSERT INTO products (product_id, name, price, stock)
    VALUES (2, 'Pen', 0.85, 50);

COMMIT;

The pen already existed, so its price became 0.85 and its stock went from 300 to 350. In MySQL 8.0.20 and later, VALUES() still works but is deprecated; the new form is INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE price = new.price. The SQL Server version uses lock hints so two users running it at the same moment can’t both insert. The full story, including MERGE and its traps, is in MERGE and Upsert in SQL Server and MySQL.

Watch out for REPLACE INTO

MySQL also has REPLACE INTO, which looks like an easy upsert. It isn’t: when the row exists, it deletes it and inserts a new one. Any column you didn’t list goes back to its default or NULL:

-- Row before: id = 1, a = 10, b = 20
REPLACE INTO t (id, a) VALUES (1, 99);
-- Row after:  id = 1, a = 99, b = NULL   (b is lost!)

Because it’s a delete, it also fires delete triggers and can trigger ON DELETE CASCADE on child tables. Prefer ON DUPLICATE KEY UPDATE.

INSERT, UPDATE and DELETE in Real Projects

The single-row examples above are where everyone starts. In business systems, most changes touch many rows at once, based on another table. These patterns come up all the time.

UPDATE using another table

A supplier sends a new price list. You load it into a table called price_updates, then update the products that appear in it. The syntax is different in each database:

-- SQL Server
UPDATE p
SET    p.price = u.new_price
FROM   products AS p
JOIN   price_updates AS u ON u.product_id = p.product_id;

-- MySQL / MariaDB
UPDATE products p
JOIN   price_updates u ON u.product_id = p.product_id
SET    p.price = u.new_price;

-- PostgreSQL
UPDATE products AS p
SET    price = u.new_price
FROM   price_updates AS u
WHERE  u.product_id = p.product_id;

Before running it, turn it into a SELECT with the same JOIN and look at old and new prices side by side. If price_updates could contain the same product twice, fix that first: with duplicates, which price “wins” is not defined.

DELETE using another table

Delete products that are out of stock and have never been ordered. The “never been ordered” part needs the order lines table:

-- Works in SQL Server, MySQL and PostgreSQL
DELETE FROM products
WHERE stock = 0
  AND NOT EXISTS (SELECT 1
                  FROM order_lines ol
                  WHERE ol.product_id = products.product_id);

NOT EXISTS is the safe choice here. NOT IN with a subquery returns nothing if the subquery contains a NULL, as explained in SQL LIKE, IN and BETWEEN.

Deleting millions of rows: work in batches

Clearing old log or history rows with one big DELETE can run for a long time, lock the table for everyone, and grow the transaction log enormously. Delete in small batches instead, so each batch is short:

-- SQL Server
DECLARE @rows INT = 1;
WHILE @rows > 0
BEGIN
    DELETE TOP (5000) FROM audit_log
    WHERE created_at < '2025-01-01';
    SET @rows = @@ROWCOUNT;
END;

-- MySQL / MariaDB: repeat until 0 rows are affected
DELETE FROM audit_log
WHERE created_at < '2025-01-01'
LIMIT 5000;

An index on created_at makes each batch fast. Run the job outside busy hours. If you need to remove all rows and nothing references the table, TRUNCATE TABLE is faster still.

Keep a record of what changed

When a user changes a price or deletes a document, someone will eventually ask “who changed this, and what was it before?” SQL Server’s OUTPUT clause returns the old and new values from the same statement, and can write them straight into a history table:

UPDATE products
SET    price = price * 1.10
OUTPUT deleted.product_id,
       deleted.price  AS old_price,
       inserted.price AS new_price,
       SYSDATETIME()
INTO   price_history (product_id, old_price, new_price, changed_at)
WHERE  product_id = 3;

In OUTPUT, deleted holds the values before the change and inserted the values after. PostgreSQL has RETURNING for the same purpose. In MySQL, use a trigger (see SQL Triggers Explained Simply) or write the history row in the same transaction.

Soft delete instead of DELETE

Many business systems never delete customers, products or documents. They mark them instead, so reports and old invoices still work:

UPDATE customers
SET    is_active = 0,
       deactivated_at = CURRENT_TIMESTAMP
WHERE  customer_id = 42;

Lists and search screens then filter on is_active = 1. In SQL Server, a filtered index such as CREATE INDEX IX_customers_active_name ON customers (name) WHERE is_active = 1; keeps those searches fast.

Habits That Keep Your Data Safe

These have saved me from disaster more than once:

  1. Run a SELECT first. Write the WHERE clause as a SELECT, check the rows it returns, then change SELECT * to UPDATE or DELETE.
  2. Use a transaction. Start with BEGIN TRANSACTION, run your change, check the result, then COMMIT or ROLLBACK. I explain this properly in SQL Transactions and ACID Explained.
  3. Filter on the primary key whenever you mean one specific row. WHERE name = 'Pen' might match more rows than you expect.
  4. Check the “rows affected” message. If you expected 1 and it says 4,000, something is wrong.
  5. Don’t experiment on production. Test on a copy first.

Quick Reference

TaskCommand
Add a rowINSERT INTO t (a, b) VALUES (1, 'x');
Add many rowsINSERT INTO t (a, b) VALUES (1,'x'), (2,'y');
Copy rows from a queryINSERT INTO t (a, b) SELECT a, b FROM s WHERE ...;
Change valuesUPDATE t SET b = 'z' WHERE a = 1;
Remove rowsDELETE FROM t WHERE a = 1;
Remove all rows fastTRUNCATE TABLE t;

Frequently Asked Questions

What is the difference between INSERT and UPDATE?

INSERT adds new rows to a table. UPDATE changes values in rows that already exist and does nothing if no row matches its WHERE clause. If you’re not sure whether the row exists, use an upsert, shown above.

Can I undo an UPDATE or DELETE?

Only if you ran it inside a transaction that you haven’t committed yet: ROLLBACK undoes it. Once it is committed (and most tools commit automatically), the only way back is a backup or a history table. That’s why the habits above matter.

How do I see how many rows were changed?

Tools like SSMS and MySQL Workbench show “rows affected” after each statement. In code, use @@ROWCOUNT in SQL Server, ROW_COUNT() in MySQL, or the value your database library returns (for example, Laravel’s update() returns the number of rows changed).

Conclusion

INSERT adds rows, UPDATE changes them and DELETE removes them. The syntax takes ten minutes to learn. The real skill is being careful: always think about your WHERE clause, check before you change, and use transactions when it matters.

If you’re still getting comfortable reading data, go back to SQL for Complete Beginners. Otherwise, the natural next step is learning how transactions protect your changes.

Related articles

Scroll to Top