INSERT RETURNING with values other than inserted ones.
От | Aleksandr Furmanov |
---|---|
Тема | INSERT RETURNING with values other than inserted ones. |
Дата | |
Msg-id | C47F63F6-B959-4A22-9F37-7A43950EB2EA@nationbuilder.com обсуждение исходный текст |
Ответы |
Re: INSERT RETURNING with values other than inserted ones.
|
Список | pgsql-general |
Hello, I want to insert new values into target table 'a' from source table 'b', and then update table 'b' with ids from table 'a',somewhat like: CREATE TABLE a(id SERIAL, name TEXT); INSERT INTO a (name) VALUES('Jason'); INSERT INTO a (name) VALUES('Peter'); CREATE TABLE b(row_id serial, id INT, name TEXT); INSERT INTO b (name) VALUES('Jason'); INSERT INTO b (name) VALUES('Peter'); WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name = name RETURNING a.id) UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id; However this would not work for obvious reason: WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot return row_id. What can be returned are only columns of 'a', but they are insufficient to identify matching records of 'b'. So the question is - what to put in WHERE clause to match RETURNING with rows being inserted from 'b'? Thanks! -- Aleksandr Furmanov
В списке pgsql-general по дате отправления: