Обсуждение: Error in Update and Set statement
Dear Folks,
How to handle the below error, please suggest. I have migrated my database from oracle to postgres through Ora2PG then I am getting this error for function.
If I am writing the code for every column which are within set (column name1, column name2, etc) then it's running successfully but if there are so many columns with set then how can I handle this because in below code there are 7 column with set statement and we have to write very big code for this. Please suggest how to handle this situation.
ERROR: syntax error at or near "SELECT"
LINE 2: ...LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LIN...
^
********** Error **********
LINE 2: ...LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LIN...
^
********** Error **********
ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 123
Code as below:
-------------------------------
update ppo_master_detail set (LINE_STATUS,LINE_TYPE,PROMISE_DATE,
LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LINE_STATUS,
LINE_TYPE,PROMISE_DATE,LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY
from ppo_master_detail where subscriber_id = 65 and row_number=supplierdetail.row_number
and po_number=supplierdetail.po_number and company_id=supplierdetail.company_id )
where po_number =PONum.po_number and company_id=PONum.company_id and subscriber_id = 65 and row_number=supplierdetail.row_number;
--iRowCounter:=iRowCounter+1;
LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LINE_STATUS,
LINE_TYPE,PROMISE_DATE,LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY
from ppo_master_detail where subscriber_id = 65 and row_number=supplierdetail.row_number
and po_number=supplierdetail.po_number and company_id=supplierdetail.company_id )
where po_number =PONum.po_number and company_id=PONum.company_id and subscriber_id = 65 and row_number=supplierdetail.row_number;
--iRowCounter:=iRowCounter+1;
--------------------------------------------
Hi
2016-01-20 11:36 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:
Dear Folks,How to handle the below error, please suggest. I have migrated my database from oracle to postgres through Ora2PG then I am getting this error for function.If I am writing the code for every column which are within set (column name1, column name2, etc) then it's running successfully but if there are so many columns with set then how can I handle this because in below code there are 7 column with set statement and we have to write very big code for this. Please suggest how to handle this situation.ERROR: syntax error at or near "SELECT"
LINE 2: ...LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LIN...
^
********** Error **********
ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 123Code as below:-------------------------------update ppo_master_detail set (LINE_STATUS,LINE_TYPE,PROMISE_DATE,
LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LINE_STATUS,
LINE_TYPE,PROMISE_DATE,LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY
from ppo_master_detail where subscriber_id = 65 and row_number=supplierdetail.row_number
and po_number=supplierdetail.po_number and company_id=supplierdetail.company_id )
where po_number =PONum.po_number and company_id=PONum.company_id and subscriber_id = 65 and row_number=supplierdetail.row_number;
--iRowCounter:=iRowCounter+1;
This Oracle's syntax is supported from 9.5.
Regards
Pavel
--------------------------------------------
Boss !!
I am using postgres 9.4, so how to handle this.
On Wed, Jan 20, 2016 at 4:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi2016-01-20 11:36 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:Dear Folks,How to handle the below error, please suggest. I have migrated my database from oracle to postgres through Ora2PG then I am getting this error for function.If I am writing the code for every column which are within set (column name1, column name2, etc) then it's running successfully but if there are so many columns with set then how can I handle this because in below code there are 7 column with set statement and we have to write very big code for this. Please suggest how to handle this situation.ERROR: syntax error at or near "SELECT"
LINE 2: ...LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LIN...
^
********** Error **********
ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 123Code as below:-------------------------------update ppo_master_detail set (LINE_STATUS,LINE_TYPE,PROMISE_DATE,
LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY) =(SELECT LINE_STATUS,
LINE_TYPE,PROMISE_DATE,LEAD_TIME,ITEM_NUMBER,ORDER_UOM_CODE,ORDER_QTY
from ppo_master_detail where subscriber_id = 65 and row_number=supplierdetail.row_number
and po_number=supplierdetail.po_number and company_id=supplierdetail.company_id )
where po_number =PONum.po_number and company_id=PONum.company_id and subscriber_id = 65 and row_number=supplierdetail.row_number;
--iRowCounter:=iRowCounter+1;This Oracle's syntax is supported from 9.5.RegardsPavel--------------------------------------------
Hi
2016-01-20 11:45 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.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
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.
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@gmail.com> wrote:
Hi2016-01-20 11:45 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.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 syntaxUPDATE tab SET a = x.a, ... FROM xRegardsPavel
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)