[SQL] update param_record set step_id = 18 where id in (select id from param_record where step_id = 11) [Err] 1093 - You can't specify target table 'param_record' for update in FROM clause
update param_record set step_id = 18 where step_id = 11orupdate param_record a inner join (select id from param_record where step_id = 11) b on a.id=b.id set a.step_id = 18
update param_record a inner join param_record b on a.id=b.id set a.step_id = 18 where b.step_id = 11
我只能更新我子查询的数据,不能一起更新step_id == 11的。 这样会影响其他的数据
update param_record a inner join (select id from param_record where step_id = 11) b on a.id=b.id set a.step_id = 18
where step_id = 11orupdate param_record a
inner join
(select id from param_record where step_id = 11) b
on a.id=b.id
set a.step_id = 18
inner join param_record b
on a.id=b.id
set a.step_id = 18
where b.step_id = 11
我只能更新我子查询的数据,不能一起更新step_id == 11的。 这样会影响其他的数据
inner join
(select id from param_record where step_id = 11) b
on a.id=b.id
set a.step_id = 18
突然发现这样更新 也存在问题。 需求: 我更新一张表里: step_id = 10和 step_id = 11和step_id = 18的数据、 他们要这样更新: 把= 10的更新成11,把==11的更新成18,把18的更新成10、 如果像我上面子查询那样更新先把:11更新成18,那下一次更新18更新10 就会把第一次更新的18全部变成10了...............怎么办?
set step_id=case step_id when 10 then 11
when 11 then 18
when 18 then 10
end
where step_id in (10,11,18)
if( step_id=11,18,if( step_id=18,10,step_id)))
ORDER BY step_id DESC;