Question

mysql表里有多条记录,把这个这里的某个字段其中一条记录值更新为1,其他为0

UPDATE vcos_wifi_config SET config_state = '1' WHERE config_id = $value;
UPADTE vcos_wifi_config SET config_state = '0' WHERE config_id NOT IN $value;
应该怎么优化
UPDATE vcos_wifi_config t1,vcos_wifi_config t2 SET t1.config_state = '1',t2.config_state = '0' WHERE t1.config_id = 1 AND t2.config_id NOT IN (1)
谢某位大神指点,用连表更新的方法可以实现

Answer 1

UPDATE vcos_wifi_config SET config_state = '1' WHERE config_id = $value;
UPADTE vcos_wifi_config SET config_state = '0' WHERE config_id != $value;

Answer 2

UPDATE vcos_wifi_config SET config_state=CASE config_id WHEN '$value' THEN 1 ELSE 0 END