Inserting Query Results

Sometimes the rows you want to add are already in the database, in one form or another. Instead of writing them out after VALUES, you can let a query find them: INSERT INTO … SELECT … adds every row that the query finds.

The shop is preparing Deluxe editions of its RPGs, each $10 more expensive than the original, out on 2026-03-01. Each of them is a copy of an existing game, with a few changes:

INSERT INTO games (title, genre, price, released, age_rating)
SELECT title || ' Deluxe', genre, price + 10, '2026-03-01', age_rating
FROM games
WHERE genre = 'RPG';

The query finds two RPGs, so the INSERT adds two games (Rows changed: 2), with the IDs 21 and 22:

The query's columns go into the listed columns by their order, not by their names: the query's first column into the first column of the list, and so on. That's why the query can have title || ' Deluxe' where the table has title, and fixed values such as '2026-03-01', which are the same in every row. The numbers must match: when the query has fewer columns than the list, you get an error such as 3 values for 4 columns.

The query can be anything you know: with WHERE, joins, GROUP BY or subqueries. Write it on its own first, run it and check its result. When it shows exactly the rows you want to add, put the INSERT INTO line above it. It's the same advice as for the WHERE of an UPDATE: look before you change.

Typical mistake: the query's columns in a different order than the list. With (customer_id, ordered_at, status) and SELECT id, 'paid', '2026-01-01', the date would go into the status. Here, the database stops it with CHECK constraint failed: status IN ('paid', 'refunded'), but two text columns without a rule would quietly swap their values.