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

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.