sqlpostgresqlsql-insert

INSERT INTO … (SELECT * …,"fixed value")

The following is an example for the requirement I have.I have 2 tables. User table and another table where I enter the food user likes.

Class User{
    long id
}

class UserFood{
    Long id (auto-generated)
    long user_id
    String food
}

In my db I have a few hundred records. Now I want to take all the user's from UserFood table who have the food "French Toast" and add "Orange Juice"

So i'm looking for an insert statement like the following

insert into user_food (user_id,food) values (select user_id from user_food where food="French Toast","Orange Juice");

When i run the above query i get the following error

syntax error at or near "select"

Is there a solution for this?

Solution

  • The following query will add orange juice records for all users who have at least one french toast record.

    INSERT INTO user_food (user_id, food)
SELECT DISTINCT user_id, 'Orange Juice'
FROM user_food
WHERE user_id IN (SELECT user_id FROM user_food WHERE food = 'French Toast')