CODEWITHSUNDEEP
sqlpostgresql
Robert Corey
Robert Corey

Reputation: 703

Get set of ID's from one table and use them to spawn a row in different table

I have 2 tables 'users' and 'settings'. I am trying to create a new row in settings for each user in users to initialize a default setting.

INSERT INTO settings
    (user_id, setting_id, value)
VALUES 
    (
        (SELECT id
        FROM users),
        16,
        true
    )

this returns the error

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

Upvotes: 1

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use insert . . . select:

INSERT INTO settings (user_id, setting_id, value)
    SELECT id, 16, true
    FROM users;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

Try this syntax.

Add the constant values to select query select list

INSERT INTO settings
    (user_id, setting_id, value)
SELECT id,16,true
FROM users

Upvotes: 1

Related Questions