Re: Re: How to convert "output deleted/inserted into" in MySQL to Postgres

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