DB2使用join语句更新查询

Question

我正在尝试更新d_wintel_healt表，下面是我正在使用的查询

UPDATE  D_WINTEL_HEALTH A
SET A.HEALTH = 7
FROM D_WINTEL_HEALTH A JOIN D_REPORTER_STATUS B ON A.HOST = B.HOST 
WHERE B.ALERTKEY = 'kph_netport_xlzc_ilo' AND 
B.SEVERITY = 0 AND 
A.HEALTH <> 0 

我还尝试了另一个查询：

UPDATE D_WINTEL_HEALTH A SET A.HEALTH = 7
FROM (Select D_WINTEL_HEALTH A JOIN D_REPORTER_STATUS B 
ON lower(A.HOST) = lower(B.HOST)) s
WHERE s.ALERTKEY = 'kph_netport_xlzc_ilo' AND s.SEVERITY = 0 AND s.HEALTH <> 0 

测试了运行良好的select查询，不确定问题是什么，在IBMDB2中是否有所不同。

Answer 1

改用MERGE语句。

MERGE INTO D_WINTEL_HEALTH A
USING D_REPORTER_STATUS B ON A.HOST = B.HOST 
AND B.ALERTKEY = 'kph_netport_xlzc_ilo' 
AND B.SEVERITY = 0 AND A.HEALTH <> 0 
WHEN MATCHED THEN UPDATE SET HEALTH = 7;