Re: Error in Update and Set statement

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(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> 2016-01-20 11:45 GMT+01:00 Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com>:
>
>> 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.