SQL 使用两个表进行更新，串联
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/542419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE using two tables, Concatenation
提问by jlrolin
I have two tables involved in this query I need to create, and I'm not exactly sure how to join these two tables in order to update.
我需要创建此查询中涉及的两个表，但我不确定如何连接这两个表以进行更新。
I have a ITEM and CONSUMER_ITEMS table. The ITEM table has a distinct code for each item and a UPC code. I need to concatenate a string with the ITEM.UPC_CODE to CONSUMER_ITEMS.NEW_ITEM_CODE where CONSUMER_ITEMS.ITEM_CODE = (Specific list of ITEM.ITEM_CODES)
我有一个 ITEM 和 CONSUMER_ITEMS 表。ITEM 表对每个项目都有一个不同的代码和一个 UPC 代码。我需要将一个带有 ITEM.UPC_CODE 的字符串连接到 CONSUMER_ITEMS.NEW_ITEM_CODE，其中 CONSUMER_ITEMS.ITEM_CODE =（ITEM.ITEM_CODES 的特定列表）
How would I go about updating the CONSUMER_ITEMS.NEW_ITEM_CODE Field?
我将如何更新 CONSUMER_ITEMS.NEW_ITEM_CODE 字段？
It would essentially be equal to 'string' || ITEM.UPC but how do I reference the CONSUMER_ITEMS.ITEM_CODE to be equal to the specific ITEM_CODE in the list of ITEM_CODES to be updated.
它本质上等于 'string' || ITEM.UPC 但我如何引用 CONSUMER_ITEMS.ITEM_CODE 以等于要更新的 ITEM_CODES 列表中的特定 ITEM_CODE。
回答by Tony Andrews
Sounds like you want:
听起来你想要：
UPDATE consumer_items ci
SET new_item_code = (SELECT 'string' || item.upc_code
FROM item
WHERE item.item_code = ci.item_code
)
WHERE ci.item_code IN ('a','b','c');
Alternatively, assuming there is a foreign key relationship between the tables and that consumer_items has a primary key, this should work:
或者，假设表之间存在外键关系并且 consumer_items 具有主键，这应该有效：
UPDATE (SELECT ci.id, ci.new_item_code, item.upc_code
FROM consumer_items ci
JOIN item ON item.item_code = ci.item_code
WHERE ci.item_code IN ('a','b','c')
) v
SET v.new_item_code = 'string' || v.upc_code
EDIT: Added WHERE clauses
编辑：添加了 WHERE 子句
回答by jlrolin
Right, that looks great but the item.item_code = ci.item_code doesn't work because:
是的，看起来不错，但是 item.item_code = ci.item_code 不起作用，因为：
SELECT distinct i.code, i.upc
FROM item i, consumer_items ci
WHERE i.ccode = '123132'
AND i.scode = 'ACTIVE'
AND i.upc IS NOT NULL
AND ci.item_code = i.code
AND i.code IN
(SELECT DISTINCT tr.item_code
FROM t_r tr
WHERE tr.category = 'RRE')
AND ci.NEW_ITEM_CODE IS NULL
This is the distinct list of CODES and UPC associated with those codes that much be used to update the CONSUMER_ITEMS.
这是与那些经常用于更新 CONSUMER_ITEMS 的代码相关联的 CODES 和 UPC 的不同列表。
new_item_code = (SELECT 'string' || item.upc_code FROM item WHERE item.item_code = (SELECT distinct i.code, i.upc
FROM item i, consumer_items ci
WHERE i.ccode = '123132'
AND i.scode = 'ACTIVE'
AND i.upc IS NOT NULL
AND ci.item_code = i.code
AND i.code IN
(SELECT DISTINCT tr.item_code
FROM t_r tr
WHERE tr.category = 'RRE')
AND ci.NEW_ITEM_CODE IS NULL));
doesn't seem to work
似乎不起作用
回答by jlrolin
The list of i.ITEM_CODE, i.UPC is this:
i.ITEM_CODE、i.UPC的列表是这样的：
014940 070182132137
018266 929245021085
018268 729245021108
018418 029245022815
018419 129245022822
018420 229245022839
018421 529245022846
018422 929245022853
The first column is ITEM CODES, Second Column is UPCs. This is on the ITEMS table.
第一列是项目代码，第二列是 UPC。这是在ITEMS表上。
The CONSUMER_ITEMS table essentially has a CONSUMER_ITEMS.ITEM_CODE as well. That's the LINK, but it also has a field called CONSUMER_ITEMS.NEW_ITEM_CODE. We want to fill the NEW_ITEM_CODE with the UPC from the corresponding ITEM_CODE in the list above with a concatentation of 'string' || UPC CODE FROM ABOVE.
CONSUMER_ITEMS 表本质上也有一个 CONSUMER_ITEMS.ITEM_CODE。那是 LINK，但它还有一个名为 CONSUMER_ITEMS.NEW_ITEM_CODE 的字段。我们想用上面列表中相应 ITEM_CODE 中的 UPC 填充 NEW_ITEM_CODE，并连接 'string' || 上面的 UPC 代码。
How we generate that list is:
我们如何生成该列表：
SELECT distinct i.code, i.upc
FROM item i, consumer_items ci
WHERE i.ccode = '123434'
AND i.scode = 'ACTIVE'
AND i.upc IS NOT NULL
AND ci.item_code = i.code
AND i.code IN
(SELECT DISTINCT tr.item_code
FROM tr_table tr
WHERE tr.category = 'RRE')
AND ci.NEW_ITEM_CODE IS NULL
This generates the ITEM_CODE, UPC list above. I need to update the CONSUMER_ITEMS that MATCH those codes above. Specifically, I need to update their NEW_ITEM_CODE fields, which are null, with the corresponding UPC concatenated with a STRING.
这将生成上面的 ITEM_CODE、UPC 列表。我需要更新与上面这些代码匹配的 CONSUMER_ITEMS。具体来说，我需要更新他们的 NEW_ITEM_CODE 字段，这些字段为空，对应的 UPC 与字符串连接。
回答by Ramesh
/*+ BYPASS_UJVC */
/*+ BYPASS_UJVC */
use this hint if you are getting the following oracle error- ORA-01779: cannot modify a column which maps to a non key-preserved table
如果您收到以下 oracle 错误 - ORA-01779：无法修改映射到非键保留表的列，请使用此提示
