我的数据库中有两个表:
表1:hussainalotcallplan1
Field Type Null Key Default Extra
------- ------------ ------ ------ ------- --------
concode varchar(20) NO PRI (NULL)
rate double YES (NULL)
vendor varchar(100) YES (NULL)
diff varchar(20) YES 'NEW'
表2:
tempratediff
Field Type Null Key Default Extra
------ ----------- ------ ------ ------- --------
id varchar(20) NO PRI (NULL)
rate double YES (NULL)
我想比较每个ID的两个表的比率,并将结果放在
diff
表的hussainalotcallplan1
列下。这两个表与
hussainalotcallplan1.concode = tempratediff.id
相关。样本数据:
hussainalotcallplan1
concode rate vendor diff 91 0.05 gbm new
tempratediff
id rate 91 0.04
我必须计算两个比率之间的差异,并将它们之间的差异插入
hussainalotcallplan1.diff
我已经试过了:
update hussainalotcallplan1 set diff = SELECT CASE WHEN (SELECT c.concode FROM hussainalotcallplan1 ) IN (SELECT t.id FROM tempratediff) THEN (c.rate-t.rate) END AS diff FROM hussainalotcallplan1 c, tempratediff t WHERE c.concode = t.id;
谁能给我一个线索吗?提前致谢。
最佳答案
您可以使用update-join语法:
UPDATE hussainalotcallplan1 h
JOIN tempratediff t ON h.concode = t.id
SET h.diff = h.rate - t.rate