如何在mysql中一次更新多行 [英] how to update mutiple rows at a time in mysql

问题描述

嗨..



如何一次更新多行。我尝试了以下更新查询。

Hi..

How to update mutiple rows at a time. i have tried with the follwing update query.

UPDATE mytable
    SET fieldName= CASE other_field
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
        WHEN 4 THEN 'value'
    END
WHERE id IN (1,2,3, 4);

此查询对我有用但我有65000条记录，如果我按照上面的查询我已经写了'时'语句为65000次。请给我一个更好的解决方案，一次更新65000条记录。





先谢谢

this query has worked for me but i have 65000 records, if i follow above query i have write 'when' statement for 65000 times. please send me the better solution for updating 65000 records at a time.


Thanks in Advance

推荐答案

UPDATE tbl_name SET field_name = COALESCE（（SELECT value FROM temp_tbl WHERE tbl_name.conditional_field = temp_tbl.condition_value），field_name），



field_name2 = COALESCE（（SELECT值FROM temp_tbl2 WHERE tbl_name.conditional_field2 = temp_tbl2.condition_value），field_name2）



尝试这个我认为它会帮助你

UPDATE tbl_name SET field_name = COALESCE((SELECT value FROM temp_tbl WHERE tbl_name.conditional_field = temp_tbl.condition_value), field_name),

field_name2 = COALESCE((SELECT value FROM temp_tbl2 WHERE tbl_name.conditional_field2 = temp_tbl2.condition_value), field_name2)

Try this i think it will help you

这篇关于如何在mysql中一次更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！