update A set A.x1 = A.x1 * (select B.y1 from B where A.ID = B.ID) ,A.x2 = A.x2 * (select B.y2 from B where A.ID = B.ID) ,A.x3 = A.x3 * (select B.y3 from B where A.ID = B.ID)
不知道你的数据库类型,但erickleung() 的方法应该可以,但速度可能不快,以下语句如果可以,那速度应该快一些:update A inner join b on a.id=b.id set A.x1 = A.x1 * B.y1 ,A.x2 = A.x2 * B.y2 ,A.x3 = A.x3 * B.y3
在oracle这样不支持update语句中带两个表名的数据库中,如下的方法更通用一点: update A set (x1,x2,x3) = (select A.x1*B.y1, A.x2*B.y2, A.x3*B.y3 from B where ID = A.ID);
update A set (x1,x2,x3) = (select A.x1*B.y1, A.x2*B.y2, A.x3*B.y3 from B where ID = A.ID);
set A.x1 = A.x1 * (select B.y1 from B where A.ID = B.ID)
,A.x2 = A.x2 * (select B.y2 from B where A.ID = B.ID)
,A.x3 = A.x3 * (select B.y3 from B where A.ID = B.ID)
set A.x1 = A.x1 * B.y1
,A.x2 = A.x2 * B.y2
,A.x3 = A.x3 * B.y3
update A
set (x1,x2,x3) = (select A.x1*B.y1, A.x2*B.y2, A.x3*B.y3
from B
where ID = A.ID);
set (x1,x2,x3) = (select A.x1*B.y1, A.x2*B.y2, A.x3*B.y3
from B
where ID = A.ID);