Home > front end >  Postgres - insert two value fields with one SELECT
Postgres - insert two value fields with one SELECT

Time:09-01

I want to do an INSERT of specific values in the defined table. This table has ManyToOne relation with team_member table. I want to set id values for two fields in logs table.

Both fields require the same id value from team_member table. That is why I repeat same SELECT for both values.

Is there a way to not to it like this, separately, and set value for both fields by one SELECT?

Note: I have one SELECT for different table, called message table and other two with team_member table.

            INSERT INTO logs
            (
             content_id,
             title,
             created_by_team_member,
             team_member_id
            )
            VALUES (
                    (SELECT id FROM message WHERE id = :contentId LIMIT 1),
                    'My Space',
                    (SELECT id FROM team_member WHERE member_id = 1 AND team_id = 5 LIMIT 1),
                    (SELECT id FROM team_member WHERE member_id = 1 AND team_id = 5 LIMIT 1)
            );

CodePudding user response:

Select nested with insert should do that.

INSERT INTO logs(title,created_by_team_member,team_member_id, content_id)
SELECT 
       'My Space' Title, 
       id created_by_team_member, 
       id team_member_id,
       (SELECT id 
          FROM message 
         WHERE id = :contentId 
         LIMIT 1) content_id
  FROM team_member WHERE member_id = 1 
   AND team_id = 5 
 LIMIT 1

  • Related