Why is this syntax invalid in postgresql?

June 23, 2023

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

>Solution :

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.