How to use IF keyword in SQL 2005

TheGorby
TheGorby used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Or, would CASE...WHEN...THEN...END be a better choice? Maybe examples of both...?
Ephraim WangoyaSoftware Engineer
Commented:
try
UPDATE #tempmain
SET PU_Confirmation = case when s.stp_status = 'DNE' then 'Y' else 'N' end
FROM stops s
inner join #tempmain on (s.ord_hdrnumber = #tempmain.OrderNumber)
where s.stp_type = 'PUP'

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
BTW, I prefer to work with the alias version:
UPDATE t
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

That works the same with an explicit INNER JOIN as shown by ewangoya.
The Update t won't work.  SQL doesn't know what t is at that point.

Author

Commented:
'UPDATE t' worked fine for me... SQL 2005 if that makes a difference

Author

Commented:
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"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

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

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
IF is a statement, while CASE returns a value. So no, you can't interchange that. On e.g. Oracle, you can use functions like  decode  to do similar things, but CASE is ANSI/ISO SQL, and should be understood by most recent DBMS.

There is no performance advantage between a "conventional" join and an ANSI Inner Join. But the ANSI Join syntax is much better to read, since you differ between the join condition (in ON), and the restriction filtering data (in WHERE). It is more important when it comes to outer joins, where the old syntax is
a) cumbersome, and prone to different interpretation by you and the DBMS
c) out-dated and obsolete, not working anymore if you set the compatibility level of MSSQL to 2008.

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial