RETURNING the rows you changed in SQL

RETURNING hands back the rows a write touched, including generated ids.

INSERT INTO tags (name)
VALUES ('sqlite')
RETURNING name;

UPDATE orders
SET status = 'shipped', shipped_at = CURRENT_TIMESTAMP
WHERE status = 'paid'
RETURNING id, status, shipped_at;

DELETE FROM order_items
WHERE quantity = 0
RETURNING order_id, sku;

How it works

1. An INSERT can return the id the engine assigned.
2. UPDATE and DELETE return the rows as they were written.
3. One statement instead of a write followed by a hopeful SELECT.

Keywords and builtins used here

• CURRENT_TIMESTAMP
• DELETE
• FROM
• INSERT
• INTO
• SET
• UPDATE
• VALUES
• WHERE

The run, in numbers

Lines

12

Characters to type

252

Tokens

46

Three-star pace

80 tpm

At the three-star pace of 80 tokens a minute, this run takes about 34 seconds.

Type this snippet

Step 8 of 8 in Tables & rows, step 21 of 23 in Language basics.

← Previous Next →