Re: Re: How to convert "output deleted/inserted into" in MySQL to Postgres - Mailing list pgsql-general

Hi Michael,

> hey, john, i did as you said like:
> update db.user
>             set deleted        = 1,
>                 updateterminal = UpdateTerminal,
>                 updateuser     = UpdateUser,
>                 updatedate     = UpdateDate
>       returning
>                 credittypeid,
>                 creditid,
>                 amount
>            into ReconDeleted
>           where deleted = 0
>             and clientid = ClientID
>             );
>
> I have ERROR:  syntax error at or near "into"

I think what you need here is a Postgres CTE, because you need to
separate the UPDATE from the INSERT. You can do your query like this:

WITH changes AS (
  update db.user
              set deleted        = 1,
                  updateterminal = UpdateTerminal,
                  updateuser     = UpdateUser,
                  updatedate     = UpdateDate
        returning
                  credittypeid,
                  creditid,
                  amount
)
INSERT INTO ReconDeleted
SELECT * FROM changes
;

(not tested, but see CTE docs if you have troubles)

Paul