I'm trying to insert some columns in a new table and set the ids in another one. I know this can be achieved using the WITH AS statement but I'm curious why it's not working this way
UPDATE "a"
SET "c1" = cp.id
FROM (
INSERT INTO "b" ("c2", "c3", "c4")
SELECT "c2", "c3", "c4"
FROM "a"
RETURNING id
) AS cp;
This is the error I'm getting:
ERROR: syntax error at or near "INTO"
LINE 4: INSERT INTO "b" ("c2", "c3", "c4")
^
SQL state: 42601
Character: 46
As per the UPDATE syntax https://www.postgresql.org/docs/current/sql-update.html, from_item should be a table expression. But the "INSERT INTO" can not be a table expression that can be specified in FROM clause.