0

I am trying to update D_WINTEL_HEALTH table and below is the query i am using 

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

I also tried with one more query:

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

But both of them return below error:

[UPDATE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=FROM;A SET A.HEALTH = 7 ;LEFT_BRACKET, DRIVER=4.15.82

tested the select query which works fine, not sure what is the issue, is it different in IBM DB2.

toofrellik
  • 1,277
  • 4
  • 15
  • 39
  • Possible duplicate of [SQL update from one Table to another based on a ID match IN db2](https://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2) – mustaccio Jun 04 '19 at 10:57

2 Answers2

1

Use MERGE statement instead.

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;
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
0

Try with below query.

 merge into AD_WINTEL_HEALTH A
   using (select SEVERITY, ALERTKEY from D_REPORTER_STATUS) b
    ON A.HOST = B.HOST
when matched and B.ALERTKEY = 'kph_netport_xlzc_ilo'  and B.SEVERITY = 0 and A.HEALTH <> 0 
   then update SET A.HEALTH = 7
Jervs
  • 344
  • 1
  • 12