CODEWITHSUNDEEP
db2
User
User

Reputation: 603

db2 - Update statement using subselect with case

I want to do an update statement according to a result of a subquery

For example :

Update TABLE1 
set A= (Select Count(*) from TABLE2 ) 
if the value of count is 0 then Update the value of A to be 0 Else set A = 1;

So could you please advice me how can I do it?

I tried the following but I got a syntax error :

SELECT count(*) as TC
 CASE
   WHEN TC > 0 
   THEN '1'
   ELSE '0'
   END  AS dum
   FROM Event E where E.Type= 'CANCELLING';

Upvotes: 3

Views: 7012

Answers (2)

Anatolii Gabuza
Anatolii Gabuza

Reputation: 6260

CASE is perfectly suitable:

UPDATE TABLE1
SET A = 
CASE 
   WHEN (SELECT count(*) FROM TABLE2) > 0 THEN 1
   ELSE 0
END

Upvotes: 5

I kiet
I kiet

Reputation: 174

declare @count int
set @count=Select Count(*) from TABLE2 

if @count=0
BEGIN
Update TABLE1 
set A=0
end
else
Update TABLE1 
set A=1

Upvotes: 1

Related Questions