MySQL更新语句仅匹配第一行 [英] MySQL update statement match only the first row

问题描述

这是我的桌子:

mysql> select * from t1;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+-------+
| id   | value |
+------+-------+
|    1 |     2 |
|    1 |     1 |
|    1 |     2 |
|    1 |     3 |
+------+-------+
4 rows in set (0.00 sec)

然后，出于某种目的，我运行sql来更新表t1中的日期:

Then ,I run a sql to update the date in table t1 for some purpose:

mysql> update t1 join t2 on t1.id=t2.id  set t1.value=t2.value ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

现在，查看更改:

 mysql> select * from t1;
+------+-------+
| id   | value |
+------+-------+
|    1 |     2 |
+------+-------+
1 row in set (0.00 sec)

我想知道为什么匹配的行数为1，并且很难理解t1的列值的值为2，其中id = 1而不是3.当匹配到第一行时，更新停止了吗? 我认为在这种情况下，它将在t1和t2之间进行完整的数据匹配. 感谢您的帮助！

I wonder that why the rows matched count is 1 ,and it's hardly understand that the column value of t1 has a value 2 where the id=1 rather than 3.Is that update stops when it matches the first row ? I think it will do a full data match across t1 and t2 in this case. Any help is appreciated!

谢谢，这是我正在认真处理的情况:

Thanks,here is the situation that I'm dealing with actully:

对于t2中的值，将它们用'，'分隔，并通过每个id合并到表t1组中的值，但是，t1中所有值都应该是不同的.例如:如表t1和t2上面的列表中，更新操作后，t1的值应为:"1,2,3"，既不是2也不是3.

For the values in t2 ,concat them seperated by ',' and the merge into the value in table t1 group by each id ,But ,all the element in t1's value should be distinct.For example: as table t1 and t2 list above , after the update operation,the t1's value should be :"1,2,3",neither 2 nor 3 .

如果我使用groupconcat()函数，将很难使t1的值与众不同.

if I use the function groupconcat(),It's will be hard to make values to be distinct for t1's value.

再次，在这种情况下，我不认为仅在行上进行更新是明智的.如果在多个表之间进行更新，则所有与联接条件匹配的行都应一个循环地进行更新.

Agin,I don't think it's clever to update only on row as in this case.If a update across multi tables ,all the rows matched by the join condition should be updated one by one in a loop.

推荐答案

根据您对问题的更新，您可以这样做

Based on your update to your question you can do it like this

UPDATE t1 JOIN
(
  SELECT id, GROUP_CONCAT(DISTINCT value ORDER BY value) value
    FROM t2
   GROUP BY id
) q
    ON t1.id = q.id
   SET t1.value = q.value

结果:

+------+-------+
| id   | value |
+------+-------+
|    1 | 1,2,3 |
+------+-------+

这里是 SQLFiddle 演示

Here is SQLFiddle demo

更新:根据您的评论，这些评论再次改变了您的问题.为了能够基于t2中的值更新t1中的定界字符串，您需要数字表(tally)的帮助来快速拆分t1.value. 您可以轻松创建这样的表格

UPDATE: Based on your comments which changed your question again. To be able to update a delimited string of values in t1 based on values in t2 you you'll need help of a numbers(tally) table to split t1.value on the fly. You can easily create such table like this

CREATE TABLE tally(n INT NOT NULL PRIMARY KEY);

INSERT INTO tally (n)
SELECT a.N + b.N * 10 + 1 n
 FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n

该脚本创建了一个表，表中包含从1到100的数字序列，这将允许有效地拆分多达100个定界值.如果需要更多或更少，则可以轻松调整脚本.

That script creates a table with a sequence of numbers from 1 to 100 which will allow to effectively split up to 100 delimited values. If you need more or less you can easily adjust the script.

现在可以更新t1.value了

UPDATE t1 JOIN
(
  SELECT id, GROUP_CONCAT(value ORDER BY value) value
    FROM
  (
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(t1.value, ',', n.n), ',', -1) value
      FROM t1 CROSS JOIN tally n
     WHERE n.n <= 1 + (LENGTH(t1.value) - LENGTH(REPLACE(t1.value, ',', '')))
     UNION
    SELECT id, value
      FROM t2
  ) v
   GROUP BY id
) q
    ON t1.id = q.id
   SET t1.value = q.value

假设您在t1

| ID | VALUE |
|----|-------|
|  1 |   1,4 |

更新结果将

| ID |   VALUE |
|----|---------|
|  1 | 1,2,3,4 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

从长远来看，所有这些都是您最好重新考虑数据库架构并规范化数据.通过允许正常维护和查询您的数据，这将节省大量时间.

That all being said in the long run you better reconsider your db schema and normalize your data. That will pay off big time by allowing normally maintain and query your data.

这篇关于MySQL更新语句仅匹配第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！