Re: Error in Update and Set statement

[Melvin Davidson <melvin6925@xxxxxxxxx>]

I believe the following will do what you want.
  
WITH poupd AS
(SELECT LINE_STATUS,
        LINE_TYPE,
         PROMISE_DATE,
        LEAD_TIME,
        ITEM_NUMBER,
        ORDER_UOM_CODE,
        ORDER_QTY
   FROM ppo_master_detail ponum,
        supplierdetail sd
  WHERE ponum.subscriber_id = 65
    AND ponum.row_number=sd.row_number
    AND ponum.po_number=sd.po_number
    AND ponum.company_id=sd.company_id
)
UPDATE ppo_master_detail pmd
 SET LINE_STATUS    = poupd.LINE_STATUS
     LINE_TYPE      = poupd.LINE_TYPE
     PROMISE_DATE   = poupd.PROMISE_DATE
     LEAD_TIME      = poupd.LEAD_TIME
     ITEM_NUMBER    = poupd.ITEM_NUMBER
     ORDER_UOM_CODE = poupd.ORDER_UOM_CODE
     ORDER_QTY      = poupd.ORDER_QTY
 FROM pmd,
      supplierdetail sdu
 WHERE pmd.po_number =poupd.po_number
   and pmd.company_id=poupd.company_id
   and pmd.subscriber_id = 65
   and pmd.row_number=sdu.row_number;
  
NOTE: Please avoid using uppercase and camelcase objects as PostgreSQL will convert them to lowercase
unless you quote them.

On Wed, Jan 20, 2016 at 5:47 AM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

Hi

2016-01-20 11:45 GMT+01:00 Sachin Srivastava <ssr.teleatlas@xxxxxxxxx>:

Boss !!

I am using postgres 9.4, so how to handle this.

I don't know Ora2Pg - try to find some option to generate in old format - or manually rewrite to supported syntax

UPDATE tab SET a = x.a, ... FROM x

Regards

Pavel

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.