使用两个表进行更新，串联

发布于 2024-07-13 04:32:40 字数 385 浏览 4 评论 0原文

我需要创建此查询中涉及的两个表，并且我不确定如何连接这两个表以进行更新。

我有一个 ITEM 和 CONSUMER_ITEMS 表。 ITEM 表中的每个项目都有一个不同的代码和一个 UPC 代码。我需要将一个字符串与 ITEM.UPC_CODE 连接到 CONSUMER_ITEMS.NEW_ITEM_CODE，其中 CONSUMER_ITEMS.ITEM_CODE =（ITEM.ITEM_CODES 的特定列表）

我将如何更新 CONSUMER_ITEMS.NEW_ITEM_CODE 字段？

它本质上等于“字符串”|| ITEM.UPC 但如何引用 CONSUMER_ITEMS.ITEM_CODE 使其等于要更新的 ITEM_CODES 列表中的特定 ITEM_CODE。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光倒影 2024-07-20 04:32:41

听起来像您想要的：

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');

或者，假设表之间存在外键关系，并且 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

编辑：添加了 WHERE 子句

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:

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

回复收藏 0 原文

温柔戏命师 2024-07-20 04:32:41

i.ITEM_CODE、i.UPC的列表是这样的：

014940  070182132137
018266  929245021085
018268  729245021108
018418  029245022815
018419  129245022822
018420  229245022839
018421  529245022846
018422  929245022853

第一列是ITEM CODES，第二列是UPC。这是在 ITEMS 表上。

CONSUMER_ITEMS 表本质上也有一个 CONSUMER_ITEMS.ITEM_CODE。这就是 LINK，但它还有一个名为 CONSUMER_ITEMS.NEW_ITEM_CODE 的字段。我们希望使用上面列表中相应 ITEM_CODE 中的 UPC 填充 NEW_ITEM_CODE，并连接“字符串” || 上面的 UPC 代码。

我们如何生成该列表：

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

这会生成上面的 ITEM_CODE、UPC 列表。我需要更新与上面这些代码匹配的 CONSUMER_ITEMS。具体来说，我需要更新其 NEW_ITEM_CODE 字段（该字段为空），并使用与 STRING 连接的相应 UPC。

The list of i.ITEM_CODE, i.UPC is this:

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.

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.

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.

回复收藏 0 原文

挽袖吟 2024-07-20 04:32:41

是的，这看起来不错，但 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

这是与那些经常用于更新 CONSUMER_ITEMS 的代码关联的代码和 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));

似乎不起作用

Right, that looks great but the item.item_code = ci.item_code doesn't work because:

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.

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

回复收藏 0 原文