SQL query involving left join - 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

SQL query involving left join

Author

Topic

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 10:03:48

Hey guys,

I have this query below that is not doing what i want it to do and can't figure out why.

The query should always update the OrderStatus column to 'AP' in tblEx1 if NOT all rows in tblEx2 with orderID = 101 are equal to either ('CA', 'OC', 'AC').

So for example, if Everything in tblEx2 with orderID = 101 has OrderStatus = 'CA' OR 'OC' OR 'AC' then OrderStatus in tblEx1 should NOT be changed to 'AP'.

But if even one row with orderID = 101 does NOT have a orderStatus = 'CA' OR 'OC' OR 'AC' then orderStatus in tblEx1 should be updated to 'AP'.

Has anyone any ideas?
Thanks


UPDATE    x
SET            x.OrderStatus = 'AP'
FROM        dbo.tblEx1 AS x LEFT JOIN
           (SELECT     OrderID
             FROM       dbo.tblEx2
            WHERE    OrderID = 101
            GROUP BY OrderID
           HAVING      SUM(CASE WHEN OrderStatus IN ('CA', 'OC', 'AC') THEN 0 ELSE 1 END) = 0) AS y ON y.OrderID = x.OrderID
          WHERE y.OrderID  IS NOT NULL

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-05 : 10:09:46

Care to post some DDL, sample data (in DML format) and expected results?

But I expect a WHERE EXISTS (SELECT * FROM tbl...)

Is probably what you are looking for

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 10:24:59

CREATE TABLE tblEx1
(OrderID INT(50),
OrderStatus char(50))

INSERT INTO tblEx1
Values('101', 'CA')

CREATE TABLE tblEx2
(OrderID INT(50),
OrderStatus char(50))

INSERT INTO tblEx2
Values('101', 'OC')

INSERT INTO tblEx2
Values('101', 'CA')

INSERT INTO tblEx2
Values('101', 'AC')

If you create these 2 tables and run the query the value for OrderStatus in tblEx1 should NOT change to AP.
But if you were to change anyone of the values in OrderStatus in tblEx2 to anythng other than 'AC', 'OC', 'CA' then it should change to AP.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-05 : 12:01:21

something like this should help,

update a
set a.OrderStatus ='AP'
from
tblEx1 a
where exists
(select 1 from tblEx2 b where b.OrderID=a.OrderID and OrderStatus not IN ('CA', 'OC', 'AC') )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 12:54:34

[code]UPDATE x
SET x.OrderStatus = 'AP'
FROM dbo.tblEx1 AS x INNER JOIN
(SELECT OrderID
FROM dbo.tblEx2
WHERE OrderID = 101
GROUP BY OrderID
HAVING SUM(CASE WHEN OrderStatus IN ('CA', 'OC', 'AC') THEN 0 ELSE 1 END) >0) AS y ON y.OrderID = x.OrderID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources