INSERT, UPDATE, DELETE and MERGE: OUTPUT, Upserts and Working Safely Against an UPDATE Without WHERE

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) against the series' Bookstore database.

Guess First

The publisher's new price list has been loaded into the price_updates table. It has a mistake: Dune was entered twice, once with the publisher's price (420.00) and once with a campaign price (380.00). The setup and the current prices:

DROP TABLE IF EXISTS dbo.price_updates;
CREATE TABLE dbo.price_updates (
    isbn      CHAR(13)      NOT NULL,
    new_price DECIMAL(10,2) NOT NULL,
    source    VARCHAR(20)   NOT NULL
);
INSERT INTO dbo.price_updates (isbn, new_price, source)
SELECT isbn, 420.00, 'publisher' FROM dbo.books WHERE book_id = 26
UNION ALL
SELECT isbn, 380.00, 'campaign'  FROM dbo.books WHERE book_id = 26
UNION ALL
SELECT isbn, 560.00, 'publisher' FROM dbo.books WHERE book_id = 27;
SELECT book_id, title, list_price FROM dbo.books WHERE book_id IN (26, 27);

book_id title        list_price
------- ------------ ----------
     26 Dune             390.50
     27 Dune Messiah     530.50

Now we update the prices with T-SQL's (Transact-SQL) join-based UPDATE syntax. Dune has two matches. What do you think happens: an error, two updates, or something else?

SET NOCOUNT OFF;
UPDATE b
SET b.list_price = p.new_price
FROM dbo.books AS b
JOIN dbo.price_updates AS p ON p.isbn = b.isbn;
SELECT book_id, title, list_price FROM dbo.books WHERE book_id IN (26, 27);

(2 rows affected)
book_id title        list_price
------- ------------ ----------
     26 Dune             420.00
     27 Dune Messiah     560.00

No error, no warning, "2 rows affected". Dune became 420.00; the campaign price was silently ignored. There is no rule deciding which row wins either: Microsoft Learn states plainly that the result is undefined in this situation. If the plan changes tomorrow, the same script may write 380.00. Try the same job with MERGE and SQL Server stops you:

MERGE dbo.books AS b
USING dbo.price_updates AS p
   ON p.isbn = b.isbn
WHEN MATCHED THEN
    UPDATE SET b.list_price = p.new_price;

Msg 8672, Level 16, State 1, Server b8be943222fa, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

This post covers the statements that change data: INSERT, UPDATE, DELETE, TRUNCATE and MERGE. For each one we ask: what does the standard say, what does T-SQL add, and where can it silently mislead you?

INSERT: Multi-Row VALUES and INSERT ... SELECT

INSERT has two basic forms. The first gives the values directly; several rows can be written in one statement (in SQL Server a single VALUES list takes at most 1000 rows):

SET NOCOUNT OFF;
INSERT INTO dbo.authors (first_name, last_name, birth_year)
VALUES (N'Italo', N'Calvino', 1923),
       (N'Jorge Luis', N'Borges', 1899);

(2 rows affected)

Always write the column list. INSERT INTO dbo.authors VALUES (...) depends on the table's column order; it breaks the day someone adds a column, or worse, writes to the wrong column if the types happen to fit. author_id is not in the list because it is an IDENTITY; SQL Server produces the value.

The second form inserts the result of a query. We create supply requests for books whose stock fell below the reorder level (reorder_requests is a table I created in my own database for this post):

DROP TABLE IF EXISTS dbo.reorder_requests;
CREATE TABLE dbo.reorder_requests (
    request_id   INT IDENTITY(1,1) PRIMARY KEY,
    book_id      INT NOT NULL,
    quantity     INT NOT NULL,
    requested_at DATETIME2(0) NOT NULL DEFAULT (SYSUTCDATETIME())
);
SET NOCOUNT OFF;
INSERT INTO dbo.reorder_requests (book_id, quantity)
SELECT s.book_id, s.reorder_level * 2 - s.quantity_on_hand
FROM dbo.stock_items AS s
WHERE s.quantity_on_hand < s.reorder_level;

(15 rows affected)

This is what set-based thinking looks like for writes: one statement instead of 15 separate INSERTs for 15 books. If you see an INSERT inside a loop in application code, it can usually be replaced with a single INSERT ... SELECT.

Getting New Ids with OUTPUT

The old way to get back the ids generated by IDENTITY is SCOPE_IDENTITY(); but it returns only the last value generated. To get all ids from a multi-row insert, use the OUTPUT clause. The inserted virtual table holds the inserted rows:

DECLARE @new_authors TABLE (author_id INT, last_name NVARCHAR(100));
INSERT INTO dbo.authors (first_name, last_name, birth_year)
OUTPUT inserted.author_id, inserted.last_name INTO @new_authors (author_id, last_name)
VALUES (N'Kazuo', N'Ishiguro', 1954),
       (N'Chimamanda Ngozi', N'Adichie', 1977);
SELECT author_id, last_name FROM @new_authors ORDER BY author_id;

author_id last_name
--------- ---------
       72 Ishiguro
       73 Adichie

OUTPUT ... INTO writes the result into a table variable, so you can use the ids in the next statement. The most typical real-life example is saving an order together with its lines:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
DECLARE @order TABLE (order_id INT);
INSERT INTO dbo.orders (customer_id, ordered_at, status)
OUTPUT inserted.order_id INTO @order (order_id)
VALUES (1, '2026-06-30 10:15:00', 'Pending');
INSERT INTO dbo.order_items (order_id, book_id, quantity, unit_price)
SELECT o.order_id, b.book_id, 1, b.list_price
FROM @order AS o
CROSS JOIN dbo.books AS b
WHERE b.book_id IN (39, 40);
COMMIT;
SELECT oi.order_id, oi.book_id, oi.quantity, oi.unit_price
FROM dbo.order_items AS oi
WHERE oi.order_id = (SELECT order_id FROM @order);

order_id book_id quantity unit_price
-------- ------- -------- ----------
     380      39        1     230.90
     380      40        1     600.00

We got the order id with OUTPUT and inserted the lines in one go with INSERT ... SELECT. Both inserts are in one transaction; thanks to SET XACT_ABORT ON, if the second one fails the first is rolled back too, and no order is left without lines. In the Dapper series post on Execute with INSERT, UPDATE and DELETE we saw reading the new id from the application side.

In other dialects RETURNING does the same job: INSERT ... RETURNING id in PostgreSQL, in SQLite since 3.35, and in MariaDB. RETURNING is not part of the ISO (International Organization for Standardization) standard; MySQL does not have it and leaves you with LAST_INSERT_ID(). We compare dialect differences together in the next post.

UPDATE ... FROM and the Standard Correlated UPDATE

The standard's UPDATE updates a single table; to look at another table you use a subquery. T-SQL, on the other hand, allows join-based updates with UPDATE ... FROM. A 10% price increase for books in the Algorithms category, two ways:

UPDATE b
SET b.list_price = b.list_price * 1.10
OUTPUT inserted.book_id, deleted.list_price AS old_price, inserted.list_price AS new_price
FROM dbo.books AS b
JOIN dbo.categories AS c ON c.category_id = b.category_id
WHERE c.name = N'Algorithms';

book_id old_price new_price
------- --------- ---------
     65   1450.00   1595.00
     66   1150.00   1265.00

UPDATE dbo.books
SET list_price = list_price * 1.10
OUTPUT inserted.book_id, deleted.list_price AS old_price, inserted.list_price AS new_price
WHERE category_id IN (SELECT category_id FROM dbo.categories WHERE name = N'Algorithms');

book_id old_price new_price
------- --------- ---------
     65   1595.00   1754.50
     66   1265.00   1391.50

Both updated the same two rows (the second applied another increase on top of the first, which is why the old prices in the output differ). Seeing the deleted (old value) and inserted (new value) virtual tables together with OUTPUT is the cheapest way to verify what an update did.

The ambiguity from the opening is specific to UPDATE ... FROM. In the standard form the new value comes from a subquery, and if the subquery returns more than one row you get error 512 from post 11 of this series. A correlated UPDATE adding supply requests to stock:

UPDATE dbo.stock_items
SET quantity_on_hand = quantity_on_hand + (SELECT SUM(r.quantity)
                                           FROM dbo.reorder_requests AS r
                                           WHERE r.book_id = stock_items.book_id)
OUTPUT inserted.book_id, deleted.quantity_on_hand AS before_qty, inserted.quantity_on_hand AS after_qty
WHERE EXISTS (SELECT 1 FROM dbo.reorder_requests AS r WHERE r.book_id = stock_items.book_id)
  AND book_id <= 20;

book_id before_qty after_qty
------- ---------- ---------
     16          4        10
     18          0        20
     19          6        20
     20          0        20

Because I used SUM here, a single value comes back even if a book has several requests; deterministic by design. I limited it to the first 20 books to keep the output short. The rule: if you write UPDATE ... FROM, make sure the join produces at most one match for each row of the target table; if you are not sure, deduplicate the source with GROUP BY first. PostgreSQL also offers a non-standard UPDATE ... FROM and carries the same ambiguity; MySQL uses the form UPDATE books AS b JOIN ... SET ....

DELETE or TRUNCATE?

Both delete rows, but they are very different tools. To see the difference I create a copy of order_items with a separate IDENTITY column:

DROP TABLE IF EXISTS dbo.order_items_archive;
SELECT CAST(order_item_id AS INT) AS order_item_id, order_id, book_id, quantity, unit_price
INTO dbo.order_items_archive
FROM dbo.order_items;
ALTER TABLE dbo.order_items_archive ADD archive_id INT IDENTITY(1,1);

Now I delete the same 682 rows once with DELETE and once with TRUNCATE, inside a transaction, read what was written to the transaction log with sys.dm_tran_database_transactions, and roll back each time:

BEGIN TRANSACTION;
DELETE FROM dbo.order_items_archive;
SELECT dt.database_transaction_log_record_count AS log_records,
       dt.database_transaction_log_bytes_used   AS log_bytes
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_current_transaction AS ct ON ct.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID();
ROLLBACK;

log_records log_bytes
----------- ---------
        773     95128

BEGIN TRANSACTION;
TRUNCATE TABLE dbo.order_items_archive;
SELECT dt.database_transaction_log_record_count AS log_records,
       dt.database_transaction_log_bytes_used   AS log_bytes
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_current_transaction AS ct ON ct.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID();
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM dbo.order_items_archive;

log_records log_bytes
----------- ---------
         15      1280
rows_after_rollback
-------------------
                682

DELETE logged every row individually: 773 log records, about 95 KB. TRUNCATE logged the deallocation of data pages rather than rows: 15 records. After the rollback the 682 rows came back; contrary to popular belief, TRUNCATE in SQL Server can be rolled back inside a transaction. Two more differences:

DELETE FROM dbo.order_items_archive;
INSERT INTO dbo.order_items_archive (order_item_id, order_id, book_id, quantity, unit_price) VALUES (1, 1, 1, 1, 1);
SELECT archive_id AS after_delete FROM dbo.order_items_archive;
TRUNCATE TABLE dbo.order_items_archive;
INSERT INTO dbo.order_items_archive (order_item_id, order_id, book_id, quantity, unit_price) VALUES (1, 1, 1, 1, 1);
SELECT archive_id AS after_truncate FROM dbo.order_items_archive;

after_delete
------------
         683
after_truncate
--------------
             1

TRUNCATE TABLE dbo.orders;

Msg 4712, Level 16, State 1, Server b8be943222fa, Line 1
Cannot truncate table 'dbo.orders' because it is being referenced by a FOREIGN KEY constraint.

TRUNCATE TABLE entered the standard with SQL:2008. It is the right tool for emptying staging tables; for deleting business data, almost never.

MERGE and Its Traps

MERGE entered the standard with SQL:2003: a single statement that matches a source against a target, updates the matches and inserts (or deletes) the non-matches. Posting a warehouse delivery to stock is a typical use; some books have a stock row, some do not:

DROP TABLE IF EXISTS dbo.stock_delivery;
CREATE TABLE dbo.stock_delivery (book_id INT NOT NULL PRIMARY KEY, quantity INT NOT NULL);
INSERT INTO dbo.stock_delivery VALUES (1, 5), (2, 3), (70, 10), (71, 4);
MERGE dbo.stock_items AS s
USING dbo.stock_delivery AS d
   ON d.book_id = s.book_id
WHEN MATCHED THEN
    UPDATE SET s.quantity_on_hand = s.quantity_on_hand + d.quantity,
               s.updated_at = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (book_id, quantity_on_hand) VALUES (d.book_id, d.quantity)
OUTPUT $action AS action, inserted.book_id, deleted.quantity_on_hand AS before_qty, inserted.quantity_on_hand AS after_qty;

action book_id before_qty after_qty
------ ------- ---------- ---------
UPDATE       1         47        52
UPDATE       2         45        48
INSERT      70       NULL        10
INSERT      71       NULL         4

OUTPUT $action shows the operation performed for each row: two updates, two inserts. Nice on paper. In practice MERGE has known traps:

If the source has several rows matching the same target row, you get error 8672 (the opening example). That is actually a good thing, but you need to deduplicate the source first. MERGE must end with a semicolon. Forget it and you get error 10713:

MERGE dbo.stock_items AS s
USING dbo.stock_delivery AS d
   ON d.book_id = s.book_id
WHEN MATCHED THEN
    UPDATE SET s.quantity_on_hand = s.quantity_on_hand + 0
PRINT 'done';

Msg 10713, Level 15, State 1, Server b8be943222fa, Line 6
A MERGE statement must be terminated by a semi-colon (;).

Concurrency. The MERGE page on Microsoft Learn says that in scenarios where unique keys are both inserted and updated, specifying HOLDLOCK prevents unique key violations, and that under heavy concurrent load separate INSERT/UPDATE logic may perform better. Without HOLDLOCK, two concurrent MERGEs can both see "no row" at the same moment and both try to insert. Over many years a large number of bug reports were published about MERGE (interactions with filtered indexes, indexed views and triggers). Most of them were fixed, but many teams prefer to use MERGE only in ETL (Extract, Transform, Load) jobs, in batches running in a single session.

Explicit Upsert: UPDATE First, Then INSERT

For a single-row upsert coming from the application I prefer this explicit recipe:

DECLARE @book_id INT = 72, @qty INT = 6;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE dbo.stock_items WITH (UPDLOCK, SERIALIZABLE)
SET quantity_on_hand = quantity_on_hand + @qty, updated_at = SYSUTCDATETIME()
WHERE book_id = @book_id;
IF @@ROWCOUNT = 0
    INSERT INTO dbo.stock_items (book_id, quantity_on_hand) VALUES (@book_id, @qty);
COMMIT;
SELECT book_id, quantity_on_hand FROM dbo.stock_items WHERE book_id = @book_id;

book_id quantity_on_hand
------- ----------------
     72                6

The Elements of Style (72) had no stock row; UPDATE affected 0 rows and INSERT ran. The UPDLOCK, SERIALIZABLE hints lock the key range the UPDATE looked at until the end of the transaction, so two concurrent requests cannot both say "not there" and insert twice. Locks, isolation levels and exactly what these hints do belong to the SQL Server Derinlemesine (SQL Server in Depth) series; what you need to know here is that an upsert is not one statement but two steps that must be protected.

Working Safely Against an UPDATE Without WHERE

The moment everyone experiences at least once on a production database: running a statement without selecting the WHERE line. We were going to raise Algorithms prices by 5%, and the WHERE was forgotten. The protective script catches it:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
DECLARE @expected INT = (SELECT COUNT(*) FROM dbo.books WHERE category_id = 12);
UPDATE dbo.books
SET list_price = list_price * 1.05;
DECLARE @affected INT = @@ROWCOUNT;
IF @affected <> @expected
BEGIN
    ROLLBACK;
    PRINT CONCAT('Rolled back: expected ', @expected, ' rows, affected ', @affected, '.');
END
ELSE
    COMMIT;
SELECT COUNT(*) AS books_priced_over_1500 FROM dbo.books WHERE list_price > 1500;

Rolled back: expected 2 rows, affected 77.
books_priced_over_1500
----------------------
                     1

The recipe: count the rows you expect with the same filter first, make the change inside a transaction, compare @@ROWCOUNT with the expectation, and ROLLBACK if they do not match. The prices of 77 books changed for a moment but were never committed; the last SELECT shows only the one book raised in the earlier examples. Add a few more habits to this recipe:

• Run every hand-written UPDATE/DELETE first as a SELECT with the same FROM and WHERE, then change the first line.
• Use SET XACT_ABORT ON; do not leave a transaction open after an error.
• When working in SSMS (SQL Server Management Studio) or Visual Studio Code (VS Code), do not leave a transaction open and go to lunch; the locks you hold make others wait.
• For changes coming from application code, always send values as parameters. The SQL Injection post of the Web Güvenliği (Web Security) series and the parameters post of the Dapper series explain why this is not negotiable.

Production Notes

Doing a large DELETE or UPDATE in a single statement bloats the transaction log and holds locks for a long time. When deleting millions of rows, splitting the work into small chunks like DELETE TOP (5000) ... WHERE ... and running them in a loop is the common approach; each chunk is committed in its own transaction. Why the split happens around 5000 (lock escalation) is in the SQL Server Derinlemesine series.

The OUTPUT clause has a restriction: if the target table has an enabled trigger, you cannot use OUTPUT without INTO. On tables with triggers, write OUTPUT ... INTO @table.

In ETL scripts that use MERGE or UPDATE ... FROM, guarantee the uniqueness of the source with a UNIQUE constraint or a check query at the start of the script. Silent ambiguities like the opening one come back months later as "where did this price come from?".

Common Pitfalls

Several source rows matching one target row in UPDATE ... FROM. No error, and which value gets written is undefined.

Not writing the column list in INSERT.

Trying to get the ids after a multi-row insert with SCOPE_IDENTITY(). It gives only the last one; use OUTPUT.

Thinking TRUNCATE is the fast version of DELETE for business data. IDENTITY resets, triggers do not fire, and it does not work at all on a table referenced by a foreign key.

Using MERGE for concurrent upserts without HOLDLOCK.

Running hand-written UPDATE/DELETE statements in production without a transaction and a row count check.

A Small Experiment

★☆☆ For customers who never placed an order (37–40), write the delete query against customers first as a SELECT, then run it as a DELETE inside a transaction, see what was deleted with OUTPUT deleted.email, and ROLLBACK.

★★☆ Write a CTE (Common Table Expression) that deduplicates the opening price_updates table so that for each ISBN (International Standard Book Number) it picks the source = 'campaign' row if there is one and the publisher row otherwise, and run UPDATE ... FROM against that CTE. Write the same job with MERGE too; error 8672 should no longer appear.

★★★ Set up an upsert scenario that runs at the same time in two separate query windows: in the first window run the UPDATE step of the explicit upsert inside BEGIN TRAN and do not commit; in the second window run the same script for the same book_id. Compare what happens with and without the UPDLOCK, SERIALIZABLE hints.

With this post we have completed the core of the query language: reading, grouping, joining, window functions and writing. In the next post we put side by side how the same queries differ across SQL Server, PostgreSQL, MySQL and SQLite, from paging to upserts, from identifier quoting to the boolean type.