Hello, I have a table in which there are columns ( u , name, etc.). The script receives data (a lot of data) with values ​​( u , name, etc.). It is necessary to update all the rows in column u with a single query. You need something like this:
UPDATE table_name SET colum_name1 = VALUES('value1', 'value2', ..., 'valuenN'), colum_name2 = VALUES('value1', 'value2', ..., 'valuenN') WHERE colum_name3 IN ('value1', 'value2', ...,'valuenN'); 
I'm afraid just like that:
UPDATE table_name JOIN ( select 'val1x' as v1, 'val1y' as v2, 'val1z' as v3 union select 'val2x', 'val2y', 'val2z' union select 'val3x', 'val3y', 'val3z' ) A ON A.v3=table_name.colum_name3 SET colum_name1 = A.v1, colum_name2 = A.v2 Or use REPLACE syntax matching INSERT, provided that colum_name3 is the primary or unique key. But at the same time the records will be deleted and inserted, which may reflect badly on the related tables.
select 'val1x' as v1, 'val1y' as v2, 'val1z' v3 The last value is missing as - KasperOption 1:
UPDATE `table` SET `name`= CASE WHEN `name`='test' THEN 'testing' WHEN `name`='test2' THEN 'testing2' WHEN `name`='test3' THEN 'testing3' ELSE `test` END ... Option 2:
INSERT INTO `table` (u, name, age) VALUES ('value1', 'value2', 5), ('value1', 'value2', 28), ('value1', 'value2', 28) ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age) U - unique key
I advise you not to use these constructions, but to perform the update through a single command in the set of transposition commands, so the blocking time for a separate record is much less and it works faster and in fact.
Source: https://ru.stackoverflow.com/questions/551429/
All Articles