Link to home
Avatar of TheGorby
TheGorbyFlag for United States of America

asked on

How to use IF keyword in SQL 2005

I'm fairly new to SQL, but being forced to learn it due to our database guy being laid off. Some of the differences in language between SQL and others (VB, Batch, AutoIT, etc.) are proving to be too different for me to easily figure out on my own.

Specifically, how can I use an IF...ELSE keyword? No matter what I do, the SELECT statement in my expression produces multiple results which causes it to fail. See my current working code below, without the use of IF...ELSE:
UPDATE #tempmain
SET PU_Confirmation = 'Y'
FROM stops s, #tempmain t
WHERE s.ord_hdrnumber = t.OrderNumber
	AND s.stp_type = 'PUP'
	AND s.stp_status = 'DNE'

UPDATE #tempmain
SET PU_Confirmation = 'N'
FROM stops s, #tempmain t
WHERE s.ord_hdrnumber = t.OrderNumber
	AND s.stp_type = 'PUP'
	AND s.stp_status <> 'DNE'

Open in new window

As you can see, they're almost identical. If the stp_status is 'DNE', set the column to 'Y'. If the stp_status is not 'DNE', set the column to 'N'. For some reason I can't get it to work without using two separate statements like this.

I suspect this is easy for those that have worked with SQL more, but if not let me know and I'll bump up the point value for this question.
Avatar of TheGorby
TheGorby
Flag of United States of America image

ASKER

Or, would CASE...WHEN...THEN...END be a better choice? Maybe examples of both...?
SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
The above is not that bad. Of course it is better to perform all changes in one go:
UPDATE #tempmain
SET PU_Confirmation = case s.stp_status when 'DNE' then 'Y' else 'N' end
FROM stops s, #tempmain t
WHERE s.ord_hdrnumber = t.OrderNumber
        AND s.stp_type = 'PUP'

Open in new window

ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
The Update t won't work.  SQL doesn't know what t is at that point.
'UPDATE t' worked fine for me... SQL 2005 if that makes a difference
ewangoya, Qlemo:

Thanks! All 3 examples work perfectly. I don't mean to start a 'battle' between you 2, but what's the advantage (if any) of using INNER JOIN vs. not using it? Does the query run faster, or anything like that?

And finally, is it possible to even use IF...ELSE in this situation? If so, even if the code is more cumbersome, I would like to know how to do it.
That is the strange part about MSSQL Update statement: You need to say
update  alias
set ...
from tbl alias

Open in new window

SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Thanks again. I too prefer code that uses less or the least amount of characters, and I see your point about the JOIN syntax being easier to read.