MySQL update select update
Questions
When using MySQL, we sometimes encounter situations where we want to update a field of some records in table A to a field of a specified record. In Oracle, we can do this:
update student t1 set t1.xx = (select t2.xx from student t2 where t2.id = 1);But in MySQL, you will get this result:
You can't specify target table 't1' for update in FROM clauseSo how do we solve it?
SolutionFor this situation, we can use inner join to solve it, like this:
update student t1 inner join (select t2.grade from student t2 where t2.id = 1) t3 set t1.grade = t3.grade;你可能想看：