Обсуждение: How to convert "output deleted/inserted into" in MySQL to Postgres
hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to convert some stored procedures in MySQL to Postgres and I came across an issue here that I can't solve:
update db.user
set Deleted = 1
, UpdateTerminal = @UpdateTerminal
, UpdateUser = @UpdateUser
, UpdateDate = @UpdateDate
output deleted.CreditTypeID
, deleted.CreditID
, deleted.Amount
into @ReconDeleted
where Deleted = 0
and ClientID = @ClientID;
I think it tried to update a couple of columns in table user and insert values in other 3 columns into another table ReconDeleted at the same time. I have issues converting "OUTPUT INTO" and "deleted" items, with my limited knowledge about Postgres, I don't think we have those in pg. Can someone tell me how to convert it? Thanks.
PS: it's my first time using this form, nice to meet you guys:)
I think it tried to update a couple of columns in table user and insert values in other 3 columns into another table ReconDeleted at the same time. I have issues converting "OUTPUT INTO" and "deleted" items, with my limited knowledge about Postgres, I don't think we have those in pg. Can someone tell me how to convert it? Thanks.
no idea what 'deleted' means, but, assuming this in is plpgsql, use UPDATE .... RETURNING ...INTO rather than OUTPUT INTO,
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
now, if this in a trigger function, you can refer to OLD. and NEW.
-- john r pierce 37N 122W somewhere on the middle of the left coast
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" ReconDeleted has three columns CreditTypeID bigint, CreditID bigint, Amount money -- View this message in context: http://postgresql.nabble.com/How-to-convert-output-deleted-inserted-into-in-MySQL-to-Postgres-tp5838762p5838771.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/20/2015 01:41 PM, Michael_LT wrote: > 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" > > ReconDeleted has three columns > CreditTypeID bigint, > CreditID bigint, > Amount money > To have this make any sense to the rest of us, you will need to show your complete function. > > > -- > View this message in context: http://postgresql.nabble.com/How-to-convert-output-deleted-inserted-into-in-MySQL-to-Postgres-tp5838762p5838771.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
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
Tong Michael wrote on 20.02.2015 21:59: > > hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to convert some stored procedures in MySQL >to Postgres and I came across an issue here that I can't solve: > > update db.user > set Deleted = 1 > , UpdateTerminal = @UpdateTerminal > , UpdateUser = @UpdateUser > , UpdateDate = @UpdateDate > output deleted.CreditTypeID > , deleted.CreditID > , deleted.Amount > into @ReconDeleted > where Deleted = 0 > and ClientID = @ClientID; > > I think it tried to update a couple of columns in table user and insert values in other 3 columns into another >table ReconDeleted at the same time. I have issues converting "OUTPUT INTO" and "deleted" items, >with my limited knowledge about Postgres, I don't think we have those in pg. Can someone tell me how to convert it? That is not valid for MySQL it does not have an "OUTPUT DELETED" option for any DML statement. Plus: MySQL does not have table variables. The looks much more like SQL Server/T-SQL.