这需要 Row_Number() 窗口功能的魔力！不幸的是，您的mysql版本是5.7；因此，使用用户定义变量的更详细的解决方案如下：

UPDATE 
table1 AS t1 

JOIN 

(SELECT 
   dt1.id, 
   IF(@cd1 = dt1.code, @rn1 := @rn1 + 1, 1) AS row_num1, 
   @cd1 := dt1.code AS code 
 FROM (SELECT id, code FROM table1 ORDER BY code, id) AS dt1
 CROSS JOIN (SELECT @rn1 := 0, @cd1 := '') AS init1
) AS t2 
  ON t2.id = t1.id 

JOIN 

(SELECT 
   IF(@cd2 = dt2.code, @rn2 := @rn2 + 1, 1) AS row_num2, 
   @cd2 := dt2.code AS code, 
   dt2.value 
 FROM (SELECT code, value FROM table2 ORDER BY code) AS dt2
 CROSS JOIN (SELECT @rn2 := 0, @cd2 := '') AS init2
) AS t3 
  ON t3.row_num2 = t2.row_num1 AND 
     t3.code = t2.code 

SET t1.value = t3.value

你可以在这个答案中查看类似技巧的解释。

madhur bhaiya提供的声明在以下情况下确实有效：
@rn被初始化为1而不是0（否则行\u num被编号为1两次）
从表2中选择是不同的（因为（代码、值）对在表2中重复）
声明应该是

UPDATE 
table1 AS t1 

JOIN 

(SELECT 
   dt1.id, 
   IF(@cd1 = dt1.code, @rn1 := @rn1 + 1, 1) AS row_num1, 
   @cd1 := dt1.code AS code,
 FROM (SELECT id, code FROM table1 ORDER BY code, id) AS dt1
 CROSS JOIN (SELECT @rn1 := 1, @cd1 := '') AS init1
) AS t2 
  ON t2.id = t1.id 

JOIN 

(SELECT 
   IF(@cd2 = dt2.code, @rn2 := @rn2 + 1, 1) AS row_num2, 
   @cd2 := dt2.code AS code, 
   dt2.value 
 FROM (SELECT DISTINCT code, value FROM table2 ORDER BY code) AS dt2
 CROSS JOIN (SELECT @rn2 := 1, @cd2 := '') AS init2
) AS t3 
  ON t3.row_num2 = t2.row_num1 AND 
     t3.code = t2.code 

SET t1.value = t3.value

使用join，因为顺序无关紧要，所以我认为您的示例输出可以更改

UPDATE table1 a 
    JOIN table2 b ON a.code= b.code 
    set a.value = b.value

使用over函数应该可以：
查询示例：

select id,code,value,rank() over (partition by code order by id asc) rank_ 
from dbo.table1;

select code,value,dense_rank() over (partition by code order by code,value asc) rank_ from dbo.table2;

更新状态：

UPDATE t 
SET t1.value = t2.value 
FROM (select id,code,value,rank() over (partition by code order by id asc) rank_ from dbo.table1) t1
inner join ( select code,value,dense_rank() over (partition by code order by code,value asc) rank_ from dbo.table2 ) t2
on t1.code = t2.code and t1.rank_ = t2.rank_