我如何更新自联接的表？ [英] how can i update a table which is self joined?
问题描述
您好，
我在Oracle中很新鲜，当我想从一个与自身连接的表中更新字段时遇到问题。
功能描述：
在表格中，可以有一个新行，应该发送给公司的客户。
为了不在每次相同的行发送，都存在一个标志（字段TO_SEND）。可以取消应该发送的行，为此在同一个表中插入一个新行。如果第一行没有发送给客户并且被取消，那么在那一刻将存在2行，其中标志TO_SEND = Y并且它们都将被发送给客户。目标是只发送第二行（取消行而不是两者）。
为此，我想使用以下脚本：
更新order_table C
设置C.TO_SEND =''N''
来自order_table C，order_table S
其中C.BU = S.BU
和C.PO_NUM = S.PO_NUM
和C.PO_LINE = S.PO_LINE
和C. CARRIER = S.CARRIER
和C.PO_DATE = S.PO_DATE
和C.PRODUCT_ID = S.PRODUCT_ID
和C.TO_SEND = S.TO_SEND
和C.BU =''Ac2''
和C.TO_SEND =''Y''
和C. STATUS ='''创造''
和S.STATUS =''抑制'';
返回的错误是：
命令行错误：3列：1
错误报告：
SQL错误：ORA-00933：SQL命令未正确结束
00933. 00000 - SQL命令未正确结束
*原因：
*行动：
有没有人知道为什么它不起作用？
谢谢。
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.
推荐答案
试试这样：
Try like this:
@ Amitpatell66：
不幸的是，你的脚本会更新满足条件的所有行，这意味着如果表格如下：
BU | PO_NUM | PO_LINE | CARRIER | PO_DATE | PRODUCT_ID | TO_SEND |状态
------------------------------------- ------------------------------------------- ------------
Ac2 | A000001 | 000001 | _____ A1 | 08.19.2010 | P00000001 | Y | CREATION
----- -------------------------------------------------- --------------------------------------------------
Ac2 | A000001 | 000001 | _____ A1 | 08.19.2010 | P00000001 | Y |抑制
将更新两行。
我需要更新只有STATUS =''CREATION''的行，而不是那个带有STATUS =''SUPPRESSION''
的那一行，谢谢你的时间。
@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.
在这种情况下试试这个：
Try this in that case:
这篇关于我如何更新自联接的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
