IF Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF Statement

Author

Topic

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-24 : 06:31:48

Hey guys,

I know it sounds basic but I'm trying to figure out a basic If statement. This what I have so far

IF ((SELECT OrderStatus
          FROM dbo.tblOrder
          WHERE OrderID = 102) = 'CA' or 'AP')   THEN                           
          UPDATE dbo.tblOrder2
	  SET  OrderStatus = @newStatus

What I'm trying to do is see if all rows with orderID = 102 have a OrderStatus = AP or CA and if they do then update the other table.
But i keep getting an error.

Can someone help with this please?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:35:36

[code]IF EXISTS(SELECT * FROM dbo.tblOrder WHERE OrderID = 102 AND OrderStatus IN ('CA', 'AP'))
UPDATE dbo.tblOrder2
SET OrderStatus = @newStatus[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:36:46

Beware that the UPDATE statement will UPDATE ALL records in tblOrder2.
Maybe we can provide us with some more information about what you are trying to do?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:38:59

Maybe this is what you want?

UPDATE		x
SET		x.OrderStatus = @newStatus
FROM		dbo.tblOrder2 AS x
INNER JOIN	(
			SELECT		OrderID
			FROM		dbo.tblOrder
			WHERE		OrderID = 102
			GROUP BY	OrderID
			HAVING		MIN(CASE WHEN OrderStatus IN ('CA', 'AP') THEN 1 ELSE 0 END) = 1
		) AS y ON y.OrderID = x.OrderID

Hehehe... We can play these guessing games all day!

E 12°55'05.25"
N 56°04'39.16"

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-24 : 06:40:02

Peso,

Thanks for the reply.

Does that query check that all of the rows with OrderID = 102 have a OrderStatus = CA or AP?

Cause if even one row doesn't then I don't want to update the next table.

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-24 : 06:44:03

haha

you were right the first time, I just need to put in the condition

where OrderID=@orderID

in the update statement

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-24 : 06:46:22

Try this

DECLARE @test TABLE ( OrderID INT ,OrderName varchar(64),OrderStatus VARCHAR(16))
DECLARE @newstatus VARCHAR(16)
SELECT @newStatus = 'NW'
INSERT INTO @test
SELECT 101,'Car','CA' UNION ALL
SELECT 102,'Bus','AP' UNION ALL
SELECT 103,'Train','GP'

UPDATE @test
SET OrderStatus = @newStatus
WHERE OrderID = 102 AND OrderStatus IN ('CA','AP')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:47:38

Rugby_fan, that's the first step!
But the update is valid if AT LEAST one record has a status of Ca or AP.
Not all of them.

Use my second suggestion if ALL RECORDS for ORDERID 102 must have a status of CA or AP.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:49:48

Raky, the original poster checks table A for a certain condition, and then wants to update table B.
Does you code provide that functionality?

E 12°55'05.25"
N 56°04'39.16"

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-24 : 06:55:24

Thanks for the help Peso.

One last thing, the query below only returns one row. What do i need to change if i want to return all rows for testing purposes?

SELECT		OrderID
      FROM		dbo.tblOrder
      WHERE		OrderID = 102
      GROUP BY	        OrderID
      HAVING		MIN(CASE WHEN OrderStatus IN ('CA', 'AP') THEN 1 ELSE 0 END) = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 07:08:21

quote:
Originally posted by rugby_fan

What do i need to change if i want to return all rows for testing purposes?

Magic. Delete the WHERE clause.

SELECT		OrderID
FROM		dbo.tblOrder
WHERE		OrderID = 102
GROUP BY        OrderID
HAVING		MIN(CASE WHEN OrderStatus IN ('CA', 'AP') THEN 1 ELSE 0 END) = 1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 08:26:02

quote:
Originally posted by Peso

Raky, the original poster checks table A for a certain condition, and then wants to update table B.
Does you code provide that functionality?

E 12°55'05.25"
N 56°04'39.16"

I've seen this before too. He never seem to read fully what OP had asked before posting a reply.

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-24 : 08:37:27

Sorry Peso,

You misunderstand me.

I ment how do I return all the rows with orderID = 102 and have a OrderStatus = AP or CA rather than just the one row which the below returns


SELECT     OrderStatus
FROM         dbo.tblOrderItems
WHERE     (OrderID = 626)
GROUP BY OrderStatus
HAVING      MIN(CASE WHEN OrderStatus IN ('CA', 'AP') THEN 1 ELSE 0 END) = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 09:48:59

quote:
Originally posted by rugby_fan

Sorry Peso,

You misunderstand me.

I ment how do I return all the rows with orderID = 102 and have a OrderStatus = AP or CA rather than just the one row which the below returns

SELECT OrderStatus
FROM dbo.tblOrderItems
WHERE (OrderID = 626)
GROUP BY OrderStatus
HAVING MIN(CASE WHEN OrderStatus IN ('CA', 'AP') THEN 1 ELSE 0 END) = 1

i think what you were asking is this

SELECT * FROM dbo.tblOrder WHERE OrderID IN
(
SELECT		OrderID
FROM		dbo.tblOrder
WHERE		OrderID = 102
GROUP BY        OrderID
HAVING		MIN(CASE WHEN OrderStatus IN ('CA', 'AP') THEN 1 ELSE 0 END) = 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 11:32:38

And add the alias at the end for the derived table, and it will work.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 11:37:52