What's with the IF in this query

Question

This is my query

update b
set b.col1 =   if(1) <= 0
          begin
        select 1 as bal
          end
          else 
        select 0 as bal

from 
dbo.table1 b
inner join  dbo.table1 a
on b.id = a.id
and b.date = a.date

The "IF" part works great by itself. Puting it in this query form throws

Incorrect syntax near the keyword 'if'.

am I missing something other than sleep.

Answer 1

You need to use a CASE WHEN statement within queries

update b
set b.col1 = CASE WHEN 1 <= 0 THEN 1 ELSE 0 END

Answer 2

how about using

set b.col1 = case when if(1) <=0 then 1 else 0 end

Answer 3

"if" is a statement, so you can't use it as an expression. You can use "case" instead:

update b
set b.col1 = case when 1 <= 0 then 1 else 0 end
from  dbo.table1 b
inner join dbo.table1 a on b.id = a.id and b.date = a.date

(The expression in your if doesn't make much sense though, as the condition always has the same value.)

Answer 4

Yeah.

In some SQL dialects there's an if that's a function. if(expresion1,expresion2,expresion3)

In some SQL dialects, there's an if ...then .. endif that's a flow-of-control construct.

Some SQL dialects have both.

That later form can generally be used only in a store procedure/function; the former wherever a function can be used.