Mysql Update Statement Match Only The First Row

Here is the my table : mysql> select * from t1; +------+-------+ | id | value | +------+-------+ | 1 | 1 | +------+-------+ 1 row in set (0.00 sec) mysql> select * f

Solution 1:

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

UPDATE t1 JOIN
(
  SELECT id, GROUP_CONCAT(DISTINCT value ORDERBY value) value
    FROM t2
   GROUPBY id
) q
    ON t1.id = q.id
   SET t1.value = q.value

Outcome:

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

Here is SQLFiddle demo

---

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

CREATETABLE tally(n INTNOTNULLPRIMARY KEY);

INSERTINTO tally (n)
SELECT a.N + b.N *10+1 n
 FROM 
(SELECT0AS N UNIONALLSELECT1UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9) a
,(SELECT0AS N UNIONALLSELECT1UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9) b
ORDERBY n

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.

Now to update t1.value you can do

UPDATE t1 JOIN
(
  SELECT id, GROUP_CONCAT(valueORDERBYvalue) valueFROM
  (
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(t1.value, ',', n.n), ',', -1) valueFROM t1 CROSSJOIN tally n
     WHERE n.n <=1+ (LENGTH(t1.value) - LENGTH(REPLACE(t1.value, ',', '')))
     UNIONSELECT id, valueFROM t2
  ) v
   GROUPBY id
) q
    ON t1.id = q.id
   SET t1.value = q.value

Assuming that you have in t1

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

outcome of the update will be

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

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.

Solution 2:

What a weird query! It seems that MySQL is clever enough to not update the same row 4 times. But besides that, on any database the result (the new value for t1.value) is undefined. You should always make sure that you update with a value of one row, or use an aggregate function (like min, max, ...)