MySQL⼀条SQL同时UPDATE多条记录普通的UPDATE语句⽤来更新⼀条数据。
UPDATE tableNmae SET columnName = value WHERE columnName = otherValue;
扩充条件的UPDATE语句可以⽤来更新多条数据，但是只能⽤来设置相同的值。
UPDATE tableNmae SET columnName = value WHERE columnName IN (otherValue1, otherValue2);
当然还有其他写法，这⾥只是举个例⼦。
使⽤⼀条UPDATE语句更新多条数据并且分别设置各⾃的值。
UPDATE tableName SET
column1 = CASE column2
WHEN column1Value1 THEN column2Value1
WHEN column1Value2 THEN column2Value2
WHEN column1Value3 THEN column2Value3
END
WHERE column2 IN (column2Value1, column2Value2, column2Value3)
⾸先要使⽤IN限定修改的数据，不使⽤IN的话在此语句中未进⾏设置值的会变成NULL或者默认值。
该语句会将column2值为相应column2Value的数据column1字段修改为对应的column1Value。
UPDATE tableName SET
column1 = CASE column2
WHEN column1Value1 THEN column2Value1mysql删除重复的数据保留一条
WHEN column1Value2 THEN column2Value2
WHEN column1Value3 THEN column2Value3
END,
column3 = CASE column2
WHEN column3Value1 THEN column2Value1
WHEN column3Value2 THEN column2Value2
WHEN column3Value3 THEN column2Value3
END
WHERE column2 IN (column2Value1, column2Value2, column2Value3)
转载⽂章