CODEWITHSUNDEEP
sqlpostgresqlsql-insert
João Correia
João Correia

Reputation: 105

SQL Insert Select when select retrieves multiple records

Tables:

workstation

user

My business requirement is: I want two new workstations, named "A" and "B" for each user that still don't have any workstation (don't have an entry in the workstation table).

So, it's like a for each.

For each user that I found in the select I want to do two inserts (for 'A' and 'B'), like

insert into workstation(name, user_id) 
values('A', select id from user where id not in (select user_id from workstation));

This doesn't work because the 'A' is hardcoded and the select retrieves multiple id's. And even if it worked for 'A', will fail for 'B' for sure.

Upvotes: 0

Views: 58

Answers (4)

Scott
Scott

Reputation: 180

Hopefully this is more accurate for you.

insert into workstation(name, user_id) 
select 'A', id from user where (select count(*) from workstation where name = 'A' and user_id = user.id) = 0
union
select 'B', id from user where (select count(*) from workstation where name = 'B' and user_id = user.id) = 0

Upvotes: 1

Ahmed Yousif
Ahmed Yousif

Reputation: 2348

You can insert multiple records from select statement directly as following

insert into workstation(name, user_id) 
select 'A', id from user where id not in (select user_id from workstation)
union
select 'B', id from user where id not in (select user_id from workstation);

to add them to 'A' and 'B' in same time union both select in same statement to ensure you add them for 'A' and 'B' workstation

for more details you can check insert in postgre documentation

Upvotes: 1

Belayer
Belayer

Reputation: 14861

insert into workstation ( name, user_id)

with ws as (select 'A' name from dual 
            union all 
            select 'B" from dual
           )
select ws.name, user_id
  from ws, users u
where not exists 
      (select null 
         from workstation w
        where w.user_id = u.user_id 
      ) ;

Note: user is a reserved word and should not be used as a table name.

Upvotes: 0

Abhishek Bajpai
Abhishek Bajpai

Reputation: 23

select id from user where id not in (select user_id from workstation) may return multiple records and i guess this is where it fails. Have you tried limiting the extracted records to only single records? Syntax might not be correct:

insert into workstation(name, user_id) values('A', (select id from user where id not in (select user_id from workstation) Limit 1))

This will get only one record that is not already present in the Workstation table even if there are multiple records for which are not already there in the workstation table.

Upvotes: 0

Related Questions