Am 04.10.2010 01:46, schrieb Scott Marlowe:
On Sun, Oct 3, 2010 at 4:14 PM, Andreas<maps...@gmx.net> wrote:
insert into staff ( company_fk, ..., department_fk )
select company_fk, ..., department_fk
from departments, companies, company_2_project AS c2p
where company_id = c2p.company_fk
and c2p.project_fk = 42
and department_id in ( 40, 50 );
step 2 would be to link those new blank staff records to project 42 by
inserting a record into staff_2_project for every new staff_id.
How can I find the new staff_ids while making sure I don't insert ids from
other sessions?
Is there an elegant way in SQL ?
Use returning?
insert into .....
yada
returning field1, field2, field3
It seams the inserts can't be chained? :(
The inner insert works when I run it separately but when I run the
chained inserts I get an syntax error.
How can a script use what RETURNING dumps out?
I tried a bit but got nowhere.
insert into staff_2_project ( staff_fk, project_fk )
insert into staff ( company_fk, ..., department_fk )
[...]
returning staff_id, 42 as project_fk;
and
insert into staff_2_project ( staff_fk, project_fk )
(
insert into staff ( company_fk, ..., department_fk )
[...]
returning staff_id, 42 as project_fk
) as s;
and
insert into staff_2_project ( staff_fk, project_fk )
select staff_id, project_fk from
(
insert into staff ( company_fk, ..., department_fk )
[...]
returning staff_id, 42 as project_fk
) as s;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
