SQL INSERT, UPDATE and DELETE without breaking data

#Why it matters

Reading data with SELECT can never hurt anyone, but SQL INSERT, UPDATE and DELETE change the data everyone else relies on, and one missing WHERE clause can rewrite every row in a table in a few milliseconds. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and the moment you are trusted with write access (fixing bad records, loading a lookup table, cleaning a staging area) you are expected to change data without breaking it. This lesson teaches the habits that make that safe: preview first, wrap it in a transaction, check the count, then commit.

#The concept

#INSERT: one row, many rows, or a query

-- one row
INSERT INTO customers (customer_id, country) VALUES (90001, 'India');

-- many rows in one statement (one round trip, one transaction)
INSERT INTO customers (customer_id, country)
VALUES (90002, 'India'), (90003, 'India'), (90004, 'Singapore');

-- the result of a query
INSERT INTO monthly_sales (month, invoices, revenue)
SELECT ...;

Always write the column list. Without it, values go into columns by position, so the statement breaks (or worse, silently writes the wrong columns) the day someone adds or reorders a column. Columns you leave out get their DEFAULT, or NULL if there is none.

#UPDATE: preview with SELECT first

An UPDATE changes every row its WHERE matches. So before running it, run the same WHERE as a SELECT and look at what comes back, including the count. If the SELECT returns 648 rows, the UPDATE must report UPDATE 648. Any other number means stop.

#UPDATE using another table

To set values from another table you join inside the UPDATE. Every database spells it differently:

UPDATE invoices AS i
SET total_amount = t.total
FROM invoice_totals AS t
WHERE t.invoice_no = i.invoice_no;

UPDATE invoices AS i
JOIN invoice_totals AS t ON t.invoice_no = i.invoice_no
SET i.total_amount = t.total;

UPDATE i
SET total_amount = t.total
FROM invoices AS i
JOIN invoice_totals AS t ON t.invoice_no = i.invoice_no;

The trap: if one target row matches several rows in the other table, the database updates it once, using one of the matches, and gives no error. Which one is not defined. Make sure the other side has one row per key (aggregate it first) before you join.

#DELETE, TRUNCATE and DROP

#RETURNING and OUTPUT

RETURNING makes an INSERT, UPDATE or DELETE hand back the rows it touched, so you get generated ids or an audit of what you removed without a second query. PostgreSQL and SQLite (3.35+) support it, MariaDB supports it on INSERT and DELETE, and SQL Server uses OUTPUT inserted.col / OUTPUT deleted.col. MySQL has no equivalent; you query before or after.

#Autocommit and transactions

Most tools (psql, pgAdmin, DBeaver, MySQL Workbench, SQL Server Management Studio) run in autocommit mode: every statement is committed the moment it finishes. There is no undo. To get one, open a transaction:

BEGIN;            -- START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server
UPDATE ...;
SELECT ...;       -- check the result
ROLLBACK;         -- or COMMIT when it is right

Inside the transaction your session sees the change, other sessions do not, and ROLLBACK puts everything back. Keep these transactions short: the rows you changed stay locked until you commit or roll back, and a forgotten open transaction blocks your colleagues. Some tools, Oracle SQL Developer among them, start with autocommit off, which turns that forgotten transaction into a common problem.

#MySQL safe update mode

MySQL Workbench turns on safe update mode by default (SQL_SAFE_UPDATES = 1; the command-line client has --safe-updates). It refuses an UPDATE or DELETE whose WHERE does not use a key column and has no LIMIT, with error 1175. People switch it off with SET SQL_SAFE_UPDATES = 0 the first time they see it. Leave it on, and write a WHERE that uses the key.

#Soft deletes and hard deletes

A hard delete removes the row. A soft delete marks it, for example discontinued_at timestamptz, and every query filters WHERE discontinued_at IS NULL, usually through a view.

• Soft delete when the row has history attached (orders, invoices), when you may need to restore it, or when audit rules apply.

• Hard delete when the data must truly go: personal data on request under India's DPDP Act or GDPR, test rows, duplicates.

• The cost of soft deletes: every query and unique constraint must remember the flag, or "deleted" rows come back in reports.

#Worked example: changing the Online Retail database safely

This continues from SQL CREATE TABLE with primary keys and foreign keys: PostgreSQL 16 with the UCI Online Retail data (541,909 lines, CC BY 4.0) in customers, products, invoices and invoice_lines. Every output below is from a real run.

CREATE TABLE raw_retail (
    invoice_no text, stock_code text, description text, quantity text,
    invoice_date text, unit_price text, customer_id text, country text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    country     text    NOT NULL
);
INSERT INTO customers (customer_id, country)
SELECT DISTINCT ON (customer_id::integer) customer_id::integer, country
FROM raw_retail
WHERE customer_id IS NOT NULL
ORDER BY customer_id::integer, to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI') DESC;

CREATE TABLE products (
    product_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    stock_code  text NOT NULL UNIQUE,
    description text
);
INSERT INTO products (stock_code, description)
SELECT stock_code, max(description) FROM raw_retail GROUP BY stock_code ORDER BY stock_code;

CREATE TABLE invoices (
    invoice_no  text        PRIMARY KEY,
    customer_id integer     REFERENCES customers (customer_id) ON DELETE RESTRICT,
    invoiced_at timestamp   NOT NULL,
    country     text        NOT NULL,
    loaded_at   timestamptz NOT NULL DEFAULT now()
);
INSERT INTO invoices (invoice_no, customer_id, invoiced_at, country)
SELECT invoice_no, min(customer_id)::integer,
       min(to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')), min(country)
FROM raw_retail GROUP BY invoice_no;

CREATE TABLE invoice_lines (
    line_id    bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    invoice_no text          NOT NULL REFERENCES invoices (invoice_no) ON DELETE CASCADE,
    product_id integer       NOT NULL REFERENCES products (product_id),
    quantity   integer       NOT NULL CHECK (quantity <> 0),
    unit_price numeric(10,3) NOT NULL CHECK (unit_price >= 0)
);
INSERT INTO invoice_lines (invoice_no, product_id, quantity, unit_price)
SELECT r.invoice_no, p.product_id, r.quantity::integer, r.unit_price::numeric
FROM raw_retail AS r JOIN products AS p ON p.stock_code = r.stock_code
WHERE r.unit_price::numeric >= 0;

CREATE INDEX ON invoice_lines (invoice_no);
CREATE INDEX ON invoice_lines (product_id);
CREATE INDEX ON invoices (customer_id);

SELECT (SELECT count(*) FROM customers) AS customers,
       (SELECT count(*) FROM products) AS products,
       (SELECT count(*) FROM invoices) AS invoices,
       (SELECT count(*) FROM invoice_lines) AS invoice_lines;

 customers | products | invoices | invoice_lines
-----------+----------+----------+---------------
      4372 |     4070 |    25900 |        541907

#Step 1: INSERT with RETURNING

INSERT INTO customers (customer_id, country)
VALUES (90001, 'India');

INSERT INTO customers (customer_id, country)
VALUES (90002, 'India'),
       (90003, 'India'),
       (90004, 'Singapore')
RETURNING customer_id, country;

INSERT INTO products (stock_code, description)
VALUES ('IN001', 'BLOCK PRINT TABLE RUNNER')
RETURNING product_id, stock_code;

INSERT INTO customers VALUES ('India', 90005);

 customer_id |  country
-------------+-----------
       90002 | India
       90003 | India
       90004 | Singapore
 
 product_id | stock_code
------------+------------
       4071 | IN001
 
ERROR:  invalid input syntax for type integer: "India"
LINE 1: INSERT INTO customers VALUES ('India', 90005);
                                      ^

RETURNING product_id handed back the id the database generated, which is how an application learns the id of the row it just created. The last insert left out the column list and put the values in the wrong order. Here the types saved us; if both columns had been text, the bad row would have gone in without a word.

#Step 2: INSERT ... SELECT to fill a summary table

CREATE TABLE monthly_sales (
    month    date          PRIMARY KEY,
    invoices integer       NOT NULL,
    revenue  numeric(14,2) NOT NULL
);

INSERT INTO monthly_sales (month, invoices, revenue)
SELECT date_trunc('month', i.invoiced_at)::date,
       count(DISTINCT i.invoice_no),
       sum(l.quantity * l.unit_price)
FROM invoices AS i
JOIN invoice_lines AS l ON l.invoice_no = i.invoice_no
WHERE i.invoice_no NOT LIKE 'C%'
GROUP BY 1;

SELECT * FROM monthly_sales ORDER BY month LIMIT 4;

   month    | invoices |  revenue
------------+----------+-----------
 2010-12-01 |     1699 | 823746.14
 2011-01-01 |     1216 | 691364.56
 2011-02-01 |     1174 | 523631.89
 2011-03-01 |     1665 | 717639.36

One statement moved the result of a whole query into a table. Because month is the primary key, running the same INSERT twice would fail instead of doubling the revenue. Loading the same data twice safely is what an upsert solves; it has its own lesson in the relational design module.

#Step 3: the safe UPDATE routine

648 product descriptions have stray spaces at the start or end, which breaks exact matches and group-bys. First, look:

SELECT product_id, stock_code,
       '[' || description || ']'       AS description_now,
       '[' || trim(description) || ']' AS after_fix
FROM products
WHERE description <> trim(description)
ORDER BY product_id
LIMIT 3;

SELECT count(*) AS rows_to_change
FROM products
WHERE description <> trim(description);

 product_id | stock_code |        description_now        |          after_fix
------------+------------+-------------------------------+------------------------------
          1 | 10002      | [INFLATABLE POLITICAL GLOBE ] | [INFLATABLE POLITICAL GLOBE]
          4 | 10123C     | [HEARTS WRAPPING TAPE ]       | [HEARTS WRAPPING TAPE]
         13 | 15030      | [FAN BLACK FRAME ]            | [FAN BLACK FRAME]
 
 rows_to_change
----------------
            648

The brackets make the invisible trailing space visible. Now the update, with the same WHERE, inside a transaction:

BEGIN;
UPDATE products
SET description = trim(description)
WHERE description <> trim(description);
SELECT count(*) AS still_untrimmed FROM products WHERE description <> trim(description);
COMMIT;

BEGIN
UPDATE 648
 still_untrimmed
-----------------
               0
COMMIT

UPDATE 648 matches the preview, and the check query confirms the fix. Only then COMMIT. This routine (preview, BEGIN, change, check the count, COMMIT) is the habit to build.

#Step 4: UPDATE from another table

Add an invoice total so reports do not have to sum lines every time:

ALTER TABLE invoices ADD COLUMN total_amount numeric(12,2);

UPDATE invoices AS i
SET total_amount = t.total
FROM (
    SELECT invoice_no, sum(quantity * unit_price) AS total
    FROM invoice_lines
    GROUP BY invoice_no
) AS t
WHERE t.invoice_no = i.invoice_no;

SELECT invoice_no, customer_id, total_amount
FROM invoices
WHERE invoice_no IN ('536365', '536366', 'C536379');

SELECT count(*) AS invoices_without_total FROM invoices WHERE total_amount IS NULL;

ALTER TABLE
UPDATE 25898
 invoice_no | customer_id | total_amount
------------+-------------+--------------
 536365     |       17850 |       139.12
 536366     |       17850 |        22.20
 C536379    |       14527 |       -27.50
 
 invoices_without_total
------------------------
                      2

The subquery is grouped by invoice_no, so each invoice matches exactly one total. Two invoices got no total: they are the two "Adjust bad debt" entries whose lines were kept out of invoice_lines by a CHECK constraint in the last lesson. An UPDATE with a join only touches rows that match, so it is worth counting the rows it skipped.

#Step 5: the multi-match trap

Now the mistake. raw_retail has many rows per stock code, and staff typed notes like "damages" into the description field for a few of them. Suppose someone "refreshes" descriptions straight from the raw table:

BEGIN;
UPDATE products AS p
SET description = r.description
FROM raw_retail AS r
WHERE r.stock_code = p.stock_code
  AND p.stock_code IN ('22423', '84879', '22139')
RETURNING p.stock_code, p.description;
ROLLBACK;

BEGIN
 stock_code |           description
------------+----------------------------------
 84879      | ASSORTED COLOUR BIRD ORNAMENT
 22139      | RETROSPOT TEA SET CERAMIC 11 PC
 22423      | REGENCY CAKESTAND 3 TIER
UPDATE 3
ROLLBACK

Looks fine. Now the same update, with the only change being that the source rows arrive in a different order:

BEGIN;
UPDATE products AS p
SET description = r.description
FROM (SELECT stock_code, description FROM raw_retail ORDER BY description DESC) AS r
WHERE r.stock_code = p.stock_code
  AND p.stock_code IN ('22423', '84879', '22139')
RETURNING p.stock_code, p.description;
ROLLBACK;

BEGIN
 stock_code | description
------------+-------------
 22139      |
 22423      | faulty
 84879      | damaged
UPDATE 3
ROLLBACK

The cake stand, one of the shop's best sellers with 2,200 lines, is now called "faulty". Stock code 22423 matched 2,203 raw rows, PostgreSQL used one of them, and nothing warned us. UPDATE 3 looked correct both times. The fix is to make the source one row per key before joining, as in Step 4 (for example the most common description per stock code).

#Step 6: forgetting the WHERE

BEGIN;
UPDATE customers SET country = 'India';
SELECT country, count(*) FROM customers GROUP BY country;
ROLLBACK;
SELECT count(DISTINCT country) AS countries_after_rollback FROM customers;

BEGIN
UPDATE 4376
 country | count
---------+-------
 India   |  4376
ROLLBACK
 countries_after_rollback
--------------------------
                       38

Every customer moved to India. UPDATE 4376 is the warning sign: you meant to change a handful of rows and the count says all of them. Because we were inside BEGIN, ROLLBACK put all 38 countries back. In autocommit mode there would be nothing to roll back, and the fix would be a restore from backup.

#Step 7: DELETE with RETURNING and USING

DELETE FROM customers
WHERE customer_id IN (90001, 90002, 90003, 90004)
RETURNING customer_id, country;

 customer_id |  country
-------------+-----------
       90001 | India
       90002 | India
       90003 | India
       90004 | Singapore
DELETE 4

To delete rows based on another table, PostgreSQL uses USING (MySQL and SQL Server use DELETE l FROM ... JOIN ...). Put RETURNING inside a CTE and you can summarise what a delete would remove before you commit to it:

BEGIN;
WITH removed AS (
    DELETE FROM invoice_lines AS l
    USING products AS p
    WHERE p.product_id = l.product_id
      AND p.stock_code = 'POST'
    RETURNING l.quantity, l.unit_price
)
SELECT count(*) AS lines_removed,
       sum(quantity * unit_price) AS postage_value
FROM removed;
ROLLBACK;

SELECT count(*) AS post_lines_still_there
FROM invoice_lines AS l
JOIN products AS p USING (product_id)
WHERE p.stock_code = 'POST';

BEGIN
 lines_removed | postage_value
---------------+---------------
          1256 |     66230.640
ROLLBACK
 post_lines_still_there
------------------------
                   1256

This is the delete-then-roll-back rehearsal: you see exactly how many rows and how much money a delete touches, and nothing changes.

#Step 8: DELETE vs TRUNCATE vs DROP

CREATE TABLE lines_copy AS SELECT * FROM invoice_lines;
\timing on
DELETE FROM lines_copy;
\timing off
INSERT INTO lines_copy SELECT * FROM invoice_lines;
\timing on
TRUNCATE lines_copy;
\timing off

INSERT INTO lines_copy SELECT * FROM invoice_lines;
BEGIN;
TRUNCATE lines_copy;
SELECT count(*) AS rows_inside_transaction FROM lines_copy;
ROLLBACK;
SELECT count(*) AS rows_after_rollback FROM lines_copy;

TRUNCATE customers;
DROP TABLE lines_copy;
SELECT count(*) FROM lines_copy;

SELECT 541907
Timing is on.
DELETE 541907
Time: 404.629 ms
Timing is off.
INSERT 0 541907
Timing is on.
TRUNCATE TABLE
Time: 13.752 ms
Timing is off.
INSERT 0 541907
BEGIN
TRUNCATE TABLE
 rows_inside_transaction
-------------------------
                       0
ROLLBACK
 rows_after_rollback
---------------------
              541907
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "invoices" references "customers".
HINT:  Truncate table "invoices" at the same time, or use TRUNCATE ... CASCADE.
DROP TABLE
ERROR:  relation "lines_copy" does not exist
LINE 1: SELECT count(*) FROM lines_copy;
                             ^

• DELETE took about 400 ms because it removes rows one by one; TRUNCATE took about 14 ms because it throws away the table's storage in one go. On a billion-row table that gap is minutes against milliseconds.

• In PostgreSQL TRUNCATE can be rolled back. Do not count on that in MySQL, where it commits immediately.

• TRUNCATE customers is refused because invoices point at customers. TRUNCATE ... CASCADE would empty invoices too.

• After DROP TABLE the table no longer exists at all.

#Step 9: a soft delete, and the bug it hid

Mark products with no sales since June 2011 as discontinued instead of deleting them, and give everyone a view of the active ones:

ALTER TABLE products ADD COLUMN discontinued_at timestamptz;

UPDATE products AS p
SET discontinued_at = '2011-12-10'
WHERE NOT EXISTS (
    SELECT 1
    FROM invoice_lines AS l
    JOIN invoices AS i ON i.invoice_no = l.invoice_no
    WHERE l.product_id = p.product_id
      AND i.invoiced_at >= '2011-06-01'
);

CREATE VIEW active_products AS
SELECT product_id, stock_code, description
FROM products
WHERE discontinued_at IS NULL;

SELECT (SELECT count(*) FROM products)        AS all_products,
       (SELECT count(*) FROM active_products) AS active_products;

ALTER TABLE
UPDATE 496
CREATE VIEW
 all_products | active_products
--------------+-----------------
         4071 |            3575

496 products are hidden but their sales history is intact, and undoing it is one UPDATE ... SET discontinued_at = NULL. But look closer at what got caught:

SELECT stock_code, description, discontinued_at FROM products WHERE stock_code = 'IN001';

 stock_code |       description        |    discontinued_at
------------+--------------------------+------------------------
 IN001      | BLOCK PRINT TABLE RUNNER | 2011-12-10 00:00:00+00

The table runner we added in Step 1 has never sold, so "no sales since June" is true for it too. The rule needed "and the product existed before June". A soft delete made this mistake cheap to undo; a hard delete would have lost the product. Whatever the delete, check a few of the rows it caught that you did not expect.

#Common mistakes

• Running an UPDATE or DELETE without previewing it. Run the WHERE as a SELECT and note the count. If the command reports a different count, roll back.

• Working in autocommit on production. Start with BEGIN, check, then COMMIT. Without a transaction your only undo is a backup restore.

• Leaving a transaction open. It holds locks and blocks other people's writes. Finish every BEGIN with COMMIT or ROLLBACK before you go for chai.

• UPDATE with a join that matches many rows. No error, an arbitrary value. Aggregate the source to one row per key first.

• INSERT without a column list. It depends on column order, which changes. Always name the columns.

• Using TRUNCATE where you meant DELETE. No WHERE, no row triggers, and in MySQL no rollback. Use it only when you really mean "empty the table".

• Turning off MySQL safe updates for good. It exists to catch the missing-WHERE accident. Write a key-based WHERE instead.

• Soft deletes that leak. If some queries read the table and not the view, "deleted" rows reappear in reports. Point everyone at the view, and remember unique constraints still see soft-deleted rows.

#Interview angle

#Practice task

The country column has three non-standard names: "EIRE", "RSA" and "USA". The sales table also contains lines that are not products at all: postage, carriage, discounts, samples, manual adjustments, bank charges, Amazon fees and charity commission.

1. Count the customers in each of the three countries.

2. In one transaction, rename them to "Ireland", "South Africa" and "United States" in both customers and invoices, check the result, and commit.

3. Rehearse deleting the non-product lines (stock codes POST, DOT, M, m, C2, D, S, BANK CHARGES, AMAZONFEE, CRUK) inside a transaction, with RETURNING summarised by stock code. Roll back and prove nothing changed.

4. Run the delete for real inside a transaction, check the remaining line count, and commit.

Done when no customer or invoice uses the old country names, and invoice_lines holds exactly the line count your rehearsal predicted.

SELECT country, count(*) AS customers
FROM customers
WHERE country IN ('EIRE', 'RSA', 'USA')
GROUP BY country ORDER BY country;

BEGIN;
UPDATE customers
SET country = CASE country
                  WHEN 'EIRE' THEN 'Ireland'
                  WHEN 'RSA'  THEN 'South Africa'
                  WHEN 'USA'  THEN 'United States'
              END
WHERE country IN ('EIRE', 'RSA', 'USA');

UPDATE invoices
SET country = CASE country
                  WHEN 'EIRE' THEN 'Ireland'
                  WHEN 'RSA'  THEN 'South Africa'
                  WHEN 'USA'  THEN 'United States'
              END
WHERE country IN ('EIRE', 'RSA', 'USA');

SELECT country, count(*) AS customers
FROM customers
WHERE country IN ('Ireland', 'South Africa', 'United States', 'EIRE', 'RSA', 'USA')
GROUP BY country ORDER BY country;
COMMIT;

 country | customers
---------+-----------
 EIRE    |         3
 RSA     |         1
 USA     |         4
BEGIN
UPDATE 8
UPDATE 368
    country    | customers
---------------+-----------
 Ireland       |         3
 South Africa  |         1
 United States |         4
COMMIT

BEGIN;
WITH removed AS (
    DELETE FROM invoice_lines AS l
    USING products AS p
    WHERE p.product_id = l.product_id
      AND p.stock_code IN ('POST', 'DOT', 'M', 'm', 'C2', 'D', 'S',
                           'BANK CHARGES', 'AMAZONFEE', 'CRUK')
    RETURNING p.stock_code, l.quantity, l.unit_price
)
SELECT stock_code, count(*) AS lines, sum(quantity * unit_price) AS value
FROM removed
GROUP BY stock_code
ORDER BY lines DESC;
ROLLBACK;
SELECT count(*) AS lines_after_rollback FROM invoice_lines;

BEGIN
  stock_code  | lines |    value
--------------+-------+-------------
 POST         |  1256 |   66230.640
 DOT          |   710 |  206245.480
 M            |   571 |  -68674.190
 C2           |   144 |    6986.000
 D            |    77 |   -5696.220
 S            |    63 |   -3049.390
 BANK CHARGES |    37 |   -7175.639
 AMAZONFEE    |    34 | -221520.500
 CRUK         |    16 |   -7933.430
 m            |     1 |       2.550
ROLLBACK
 lines_after_rollback
----------------------
               541907

BEGIN;
DELETE FROM invoice_lines AS l
USING products AS p
WHERE p.product_id = l.product_id
  AND p.stock_code IN ('POST', 'DOT', 'M', 'm', 'C2', 'D', 'S',
                       'BANK CHARGES', 'AMAZONFEE', 'CRUK');
SELECT count(*) AS lines_left FROM invoice_lines;
COMMIT;

BEGIN
DELETE 2909
 lines_left
------------
     538998
COMMIT

#Further reading

• PostgreSQL: UPDATE: FROM, RETURNING, and the warning about joins that match several rows.

• PostgreSQL: DELETE and TRUNCATE: USING, RESTART IDENTITY and CASCADE.

• MySQL: TRUNCATE TABLE: why it causes an implicit commit and how it differs from DELETE.

• MySQL: safe updates mode: exactly which statements it blocks.

• SQL Server: OUTPUT clause: the SQL Server version of RETURNING.