By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

how can i update a table which is self joined?

P: 8
Hello,

I am fresh in Oracle and I encounter a problem when I want to update a field from a table which is joined with itself.

Functional description:

Into a table, it is possible to have a new line which should be sent to the company's customers.
In order to not be sent each time same line, there exists a flag (field TO_SEND). The line which should be sent can be canceled and for this a new line is inserted in same table. If 1st line wasn't sent to the customer and it is canceled, in that moment will exists 2 lines with flag TO_SEND = Y and both of them will be sent to customer. The goal is to send only the 2nd line (the cancel line and not both of them).

For this, I was thinking to use the following script:

update order_table C
set C.TO_SEND = 'N'
from order_table C, order_table S
where C.BU = S.BU
and C.PO_NUM = S.PO_NUM
and C.PO_LINE = S.PO_LINE
and C.CARRIER = S.CARRIER
and C.PO_DATE = S.PO_DATE
and C.PRODUCT_ID = S.PRODUCT_ID
and C.TO_SEND = S.TO_SEND
and C.BU = 'Ac2'
and C.TO_SEND = 'Y'
and C.STATUS = 'CREATION'
and S.STATUS = 'SUPPRESSION';

The error returned is:

Error at Command Line:3 Column:1
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

Do, someone have any clue why it doesn't work?

Thanks.
Aug 19 '10 #1
Share this Question
Share on Google+
10 Replies


amitpatel66
Expert 100+
P: 2,367
Try like this:

Expand|Select|Wrap|Line Numbers
  1. update order_table C
  2. set C.TO_SEND = 'N'
  3. WHERE UPPER(C.BU) = 'AC2'
  4. and   C.TO_SEND = 'Y'
  5. and   C.STATUS IN ('CREATION','SUPPRESSION');
  6.  
  7.  
Aug 19 '10 #2

P: 8
@Amitpatell66:

unfortunately, your script updates all lines which satisfy the conditions, meaning if The table look like this:

BU |PO_NUM|PO_LINE|CARRIER|PO_DATE |PRODUCT_ID|TO_SEND|STATUS
--------------------------------------------------------------------------------------------
Ac2|A000001|000001|_____A1|08.19.2010|P00000001 |Y|CREATION
---------------------------------------------------------------------------------------------------------
Ac2|A000001|000001|_____A1|08.19.2010|P00000001 |Y|SUPPRESSION

will update both lines.

I would need to update only the line with STATUS = 'CREATION' and not also the one with STATUS = 'SUPPRESSION'

anyway, thanks for your time.
Aug 19 '10 #3

amitpatel66
Expert 100+
P: 2,367
Try this in that case:

Expand|Select|Wrap|Line Numbers
  1. update order_table C 
  2. set C.TO_SEND = 'N' 
  3. WHERE UPPER(C.BU) = 'AC2' 
  4. and   C.TO_SEND = 'Y' 
  5. and   C.STATUS = 'CREATION';
  6.  
Aug 19 '10 #4

P: 8
This is working in case when I have only the 1st line but i want to be done in case when there exists both lines (not only one of them).
Aug 19 '10 #5

amitpatel66
Expert 100+
P: 2,367
Can you please post some sample data and the sample expected output? Also please explain what your below statement means:

"This is working in case when I have only the 1st line but i want to be done in case when there exists both lines (not only one of them). "
Aug 19 '10 #6

P: 8
the example data are in my 2nd post.

BU |PO_NUM|PO_LINE|CARRIER|PO_DATE |PRODUCT_ID|TO_SEND|STATUS
--------------------------------------------------------------------------------------------
Ac2|A000001|000001|_____A1|08.19.2010|P00000001 |Y|CREATION
---------------------------------------------------------------------------------------------------------
Ac2|A000001|000001|_____A1|08.19.2010|P00000001 |Y|SUPPRESSION

the select i want to create should return only 1st line.

my comment ..."This is working in case when I have only the 1st line but i want to be done in case when there exists both lines (not only one of them). "...refers to the fact that the select should be used only in case when there exists the 2nd line too and not only the 1st line (both lines must exists for an order).
Aug 19 '10 #7

amitpatel66
Expert 100+
P: 2,367
Ok. Thanks for the explanation.

Can you try this update:

Expand|Select|Wrap|Line Numbers
  1.  
  2. update order_table C  
  3. set C.TO_SEND = 'N'  
  4. WHERE UPPER(C.BU) = 'AC2'  
  5. and   C.TO_SEND = 'Y'  
  6. and   C.STATUS = 'CREATION'
  7. AND   2 = (SELECT COUNT(status) FROM order_table s WHERE
  8. s.PO_NUM = c.PO_NUM
  9. and s.PO_LINE = c.PO_LINE
  10. and s.CARRIER = c.CARRIER
  11. and s.PO_DATE = c.PO_DATE
  12. and s.PRODUCT_ID = c.PRODUCT_ID
  13. and s.TO_SEND = c.TO_SEND
  14. and UPPER(s.BU) = 'AC2' 
  15. and s.TO_SEND = 'Y') 
  16.  
Aug 19 '10 #8

P: 8
sorry, it is still not working.
the error is:
SQL Error: ORA-00933: SQL command not properly ended
Aug 20 '10 #9

amitpatel66
Expert 100+
P: 2,367
Are you still trying your old query?

The syntax for the query that i provided is correct.

For Ex:

Expand|Select|Wrap|Line Numbers
  1. SQL> ed
  2. Wrote file afiedt.buf
  3.  
  4.   1  update emp e set salary = 100020
  5.   2  from 2 = (SELECT COUNT(empid) from emp s
  6.   3* where s.empid = e.empid)
  7. SQL> /
  8. from 2 = (SELECT COUNT(empid) from emp s
  9. *
  10. ERROR at line 2:
  11. ORA-00933: SQL command not properly ended
  12.  
  13.  
  14. SQL> ed
  15. Wrote file afiedt.buf
  16.  
  17.   1  update emp e set salary = 100020
  18.   2  where 2 = (SELECT COUNT(empid) from emp s
  19.   3* where s.empid = e.empid)
  20. SQL> /
  21.  
  22. 0 rows updated.
  23.  
  24. SQL> 
  25.  
Aug 20 '10 #10

P: 8
nop, sorry.
case resolved
Sep 1 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.