In postgresql I want to do something like this
update users set salary_account_id =
(insert into salary_accounts(some_field) values(1) returning salary_account_id)
I want for every record in user table to create corresponding salary_account record and then update users table with corresponding newly created salary_account_id
If I run this query I get an error 'syntax error at or near "into"'.
Two combine the two statements into one, you need to use a CTE:
with inserted as (
insert into salary_accounts(some_field)
values (1)
returning salary_account_id
)
update users
set salary_account_id = (select salary_account_id from inserted)
where ???
You will have to add a WHERE clause to restrict the users you want to update as I can't imagine you want to update all users to the same salary_account_id.
You need to use two separate queries. something like this:
insert into salary_accounts(some_field, user_fk, ...)
select 1, user_uid, .. from users;
update update users u
set salary_account_id = (select salary_account_id from salary_accounts where user_fk = user_uid);
Or If you do not have the user_fk in salary_accounts table then you will need something like this:
update update users u
set salary_account_id = some_sequence;
insert into salary_accounts(some_field, salary_account_id , ...)
select 1, salary_account_id , .. from users;
CREATE OR REPLACE FUNCTION CreateSalaryAccount()
RETURNS integer AS $func$
declare
a_id integer;
BEGIN
insert into salary_accounts(some_val) values(1) returning salary_account_id into a_id;
RETURN a_id;
END;
$func$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION UpdateUsersTable()
RETURNS VOID
AS
$$
DECLARE
t_row users%rowtype;
BEGIN
FOR t_row in SELECT * FROM users LOOP
update users
set salary_account_id = CreateSalaryAccount()
where id = t_row.id;
END LOOP;
END;
$$
LANGUAGE plpgsql;
select UpdateUsersTable()
insertinto a function, which would do the insert and return the new ID. This can't be done as written.usersandsalary_account? I can't imagine you want to change all users to the salary_account_id you have just inserted