Re: [GENERAL] Re: How to convert output deleted/inserted into in MySQL to Postgres

2015-02-20 Thread Adrian Klaver 

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: How to convert output deleted/inserted into in MySQL to Postgres

2015-02-20 Thread Michael_LT 
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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: How to convert output deleted/inserted into in MySQL to Postgres

2015-02-20 Thread Paul Jungwirth 

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general