使用来自另一个表的连接数据进行 SQL 更新 [英] SQL Update with concatenated data from another table

问题描述

我一直迷失在这个方法中，并努力寻找正确的方法.希望那里的人可能知道做我想做的事情的好方法.我有两个表，我想使用来自另一个表的连接数据更新一个表，其中 id 相同.举个例子....

I'm keep getting lost in this one and struggling to find the right method. Hopefully someone out there might know of a good way of doing what I want to do. I have two tables and I want to update one table using concatenated data from the other where the ids are the same. As an example....

表 1

  ItemID       CategoryID
       1                20
       1                30
       1                40
       2                10
       3                40
       3                20
       4                10
       4                20

表 2

  ItemID        CatIDs
       1
       2
       3
       4

我想用 Table1 中与 ItemID 匹配的所有类别 ID 更新 Table2.CatIDs.当我像这样写下来时似乎很简单，但是在尝试了我在网上找到的内部联接、子查询等之后，我不断收到您的 SQL 语法有错误..."

I want to update Table2.CatIDs with all the Category IDs from Table1 where the ItemIDs match. It seems straightforward when I write it down like that but after trying Inner Joins, Sub Queries and so on as I've found online, I keep getting "You have errors in your SQL Syntax..."

我希望 Table2 看起来像

I want Table2 to look something like

   ItemID        CatIDs
        1        20,30,40
        2        10
        3        40,20
        4        10,20

我尝试过内部连接和子查询，最接近的没有错误的是这个......

I've tried Inner Joins and also sub queries and the closest I've got without an error was this....

  UPDATE Table2
  SET Table2.CatIDs = Table2.CatIDs + ", " + 
  (SELECT CategoryID FROM Table1 WHERE Table2.ItemID = Table1.ItemID)

但它似乎还没有完成，它所做的只是用相同的 CatID 更新四行，然后给我消息#1242 - 子查询返回超过 1 行

But it doesn't seem finished and all it done was update four rows with the same CatIDs and then give me the message #1242 - Subquery returns more than 1 row

我相信外面的人能够看到我哪里出错了，并为我指明了正确的方向.

I'm sure someone out there will be able to see where I'm going wrong and point me in the right direction.

提前致谢

推荐答案

尝试像下面这样使用 group_concat() 你可以得到 , 分隔的列表，然后在它们之间加入桌子.但是存储逗号分隔值从来都不是一个好主意.

Try like below by using group_concat() you can get the , separated list and then join between the tables. But storing comma separated values is never a good idea.

update table2 t2
join
(
select ItemID,group_concat(CategoryID) as newcat
from table1 group by ItemID
) tab on t2.ItemID = tab.ItemID        
set t2.CatIDs = tab.newcat

这篇关于使用来自另一个表的连接数据进行 SQL 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！