通过从两个表连接来更新所有字段值

I have two MySQL tables:

我有两个MySQL表:

  Table items            Table buffer
---------------         -------------
id     keywords         id      value
1      val1             1       val2
2      val1             2       val2
3      val1             3       val2

Both keywords and value are VARCHAR(250). Now I want to update all keywords in table items like this:

关键字和值都是VARCHAR(250)。现在我想更新表格项中的所有关键字,如下所示:

   Table items
-----------------
id     keywords
1      val1, val2
2      val1, val2
3      val1, val2

I tried to achieve this with CONCAT but obviously I'm doing something wrong. Can you please help me.

我尝试用CONCAT实现这一点,但显然我做错了。你能帮我么。

2 个解决方案

#1

You need to UPDATE with JOIN like so:

您需要使用JOIN更新,如下所示:

UPDATE items i
INNER JOIN buffer b ON i.id = b.id
SET i.keywords = CONCAT(i.keywords, ', ', b.value);

SQL Fiddle Demo

#2

you can do this way as well:

你也可以这样做:

UPDATE items, buffer 
SET items.keywords = CONCAT(items.keywords, buffer.value) 
where items.id = buffer.id;