Avatar of TheGorby
TheGorby
Flag 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.
Microsoft SQL ServerMicrosoft SQL Server 2005Programming

Avatar of undefined
Last Comment
TheGorby

8/22/2022 - Mon
TheGorby

ASKER
Or, would CASE...WHEN...THEN...END be a better choice? Maybe examples of both...?
SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Qlemo

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DrewKjell

The Update t won't work.  SQL doesn't know what t is at that point.
Your help has saved me hundreds of hours of internet surfing.
fblack61
TheGorby

ASKER
'UPDATE t' worked fine for me... SQL 2005 if that makes a difference
TheGorby

ASKER
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.
Qlemo

That is the strange part about MSSQL Update statement: You need to say
update  alias
set ...
from tbl alias

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TheGorby

ASKER
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.