Is it really no solution to update multiple records in MySQL?

Question

I want to do all these update in one statement.

update table set ts=ts_1 where id=1
update table set ts=ts_2 where id=2
...
update table set ts=ts_n where id=n

Is it?

Answer 1

Yes you can but that would require a table (if only virtual/temporary), where you's store the id + ts value pairs, and then run an UPDATE with the FROM syntax.
Assuming tmpList is a table with an id and a ts_value column, filled with the pairs of id value, ts value you wish to apply.

UPDATE table, tmpList
  SET table.ts = tmpList.ts_value
WHERE table.id = tmpList.id
  -- AND table.id IN (1, 2, 3, .. n)  
  -- above "AND" is only needed if  somehow you wish to limit it, i.e 
  -- if tmpTbl has more idsthan you wish to update

A possibly table-less (but similar) approach would involve a CASE statement, as in:

UPDATE table
  SET ts = CASE id
      WHEN 1 THEN 'ts_1'
      WHEN 2 THEN 'ts_2'
      -- ..
      WHEN n THEN 'ts_n'
  END
  WHERE id in (1, 2, ... n)  -- here this is necessary I believe

Answer 2

ts_1, ts_2, ts_3, etc. are different fields on the same table? There's no way to do that with a single statement.

Answer 3

Use this:

UPDATE `table` SET `ts`=CONCAT('ts_', `id`);

Answer 4

Well, without knowing what data, I'm not sure whether the answer is yes or no.

It certainly is possible to update multiple rows at once:

update table table1 set field1='value' where field2='bar'

This will update every row in table2 whose field2 value is 'bar'.

update table1 set field1='value' where field2 in (1, 2, 3, 4)

This will update every row in the table whose field2 value is 1, 2, 3 or 4.

update table1 set field1='value' where field2 > 5

This will update every row in the table whose field2 value is greater than 5.

update table1 set field1=concat('value', id)

This will update every row in the table, setting the field1 value to 'value' plus the value of that row's id field.

Answer 5

You could do it with a case statement, but it wouldn't be pretty:

UPDATE table
SET ts = CASE id WHEN 1 THEN ts_1 WHEN 2 THEN ts_2 ... WHEN n THEN ts_n END

Answer 6

I think that you should expand the context of the problem. Why do you want/need all the updates to be done in one statement? What benefit does that give you? Perhaps there's another way to get that benefit.
Presumably you are interacting with sql via some code, so certainly you can simply make sure that the three updates all happen atomically by creating a function that performs all three of the updates.

e.g. pseudocode:

function update_all_three(val){
// all the updates in one function
}

The difference between a single function update and some kind of update that performs multiple updates at once is probably not a very useful distinction.

Answer 7

generate the statements:

select concat('update table set ts = ts_', id, ' where id = ', id, '; ') 
from table

or generate the case conditions, then connect it to your update statement:

select concat('when ', id, ' then ts_', id) from table