how can i update a table which is self joined?

[mikcutu]

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.

[amitpatel66]

Try like this:

update order_table C
set C.TO_SEND = 'N'
WHERE UPPER(C.BU) = 'AC2'
and   C.TO_SEND = 'Y'
and   C.STATUS IN ('CREATION','SUPPRESSION');
 
 

[mikcutu]

@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.

[amitpatel66]

Try this in that case:

update order_table C 
set C.TO_SEND = 'N' 
WHERE UPPER(C.BU) = 'AC2' 
and   C.TO_SEND = 'Y' 
and   C.STATUS = 'CREATION';
 

[mikcutu]

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).

[amitpatel66]

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). "

[mikcutu]

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).

[amitpatel66]

Ok. Thanks for the explanation.

Can you try this update:

 
update order_table C  
set C.TO_SEND = 'N'  
WHERE UPPER(C.BU) = 'AC2'  
and   C.TO_SEND = 'Y'  
and   C.STATUS = 'CREATION'
AND   2 = (SELECT COUNT(status) FROM order_table s WHERE
s.PO_NUM = c.PO_NUM
and s.PO_LINE = c.PO_LINE
and s.CARRIER = c.CARRIER
and s.PO_DATE = c.PO_DATE
and s.PRODUCT_ID = c.PRODUCT_ID
and s.TO_SEND = c.TO_SEND
and UPPER(s.BU) = 'AC2' 
and s.TO_SEND = 'Y') 
 

[mikcutu]

sorry, it is still not working.
the error is:
SQL Error: ORA-00933: SQL command not properly ended

[amitpatel66]

Are you still trying your old query?

The syntax for the query that i provided is correct.

For Ex:

SQL> ed
Wrote file afiedt.buf
 
  1  update emp e set salary = 100020
  2  from 2 = (SELECT COUNT(empid) from emp s
  3* where s.empid = e.empid)
SQL> /
from 2 = (SELECT COUNT(empid) from emp s
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
 
 
SQL> ed
Wrote file afiedt.buf
 
  1  update emp e set salary = 100020
  2  where 2 = (SELECT COUNT(empid) from emp s
  3* where s.empid = e.empid)
SQL> /
 
0 rows updated.
 
SQL> 
 

[mikcutu]

nop, sorry.
case resolved