Changing condition to negative

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

Changing condition to negative

Author

Topic

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-28 : 06:56:23

Hey guys,

This is the query I have


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      MIN(CASE WHEN OrderStatus IN ('CA', 'OC', 'AC') THEN 1 ELSE 0 END) = 1) AS y ON y.OrderID = x.OrderID

It's the HAVING line i need to change i think.

I want to set OrderStatus = AP if not all the rows in tblEX2 with orderID = 101 are equal to either 'CA', 'OC', 'AC'.

Anotehr way to put it is that every row in tblEx2 with OrderID = 101 has to have a status equal to either 'CA', 'OC', 'AC' in order for the status column in tblEx1 to change to 'AP'.

Can someone help with this?
Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-28 : 07:15:03

Here's an example to run...

declare @tblEX1 table (OrderID int, OrderStatus char(2))
declare @tblEX2 table (OrderID int, OrderStatus char(2))

insert @tblEX1 (OrderID)
          select 101
union all select 2
union all select 3
union all select 4

insert @tblEX2
          select 101, 'CA'
union all select 2, 'OC'
union all select 2, 'AC'
union all select 3, 'CA'
union all select 3, 'CA'
union all select 3, 'OC'
union all select 3, 'CA'
union all select 4, 'CA'
union all select 4, 'ZZ'

UPDATE    x
SET            x.OrderStatus = 'AP'
FROM        @tblEX1 AS x INNER JOIN
           (select OrderID from @tblEX2 group by OrderID having count(*) = 
                sum(CASE WHEN OrderStatus IN ('CA', 'OC', 'AC') THEN 1 ELSE 0 END)
) AS y ON y.OrderID = x.OrderID

select * from @tblEX1

/*
OrderID     OrderStatus
----------- -----------
101         AP
2           AP
3           AP
4           NULL
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-28 : 07:20:13

I think you can simplify the update query to...

update @tblEX1 set OrderStatus = 'AP' where OrderId in
    (select OrderID from @tblEX2 group by OrderID having count(*) = 
        sum(CASE WHEN OrderStatus IN ('CA', 'OC', 'AC') THEN 1 ELSE 0 END))

So you want...

update dbo.tblEX1 set OrderStatus = 'AP' where OrderId in
    (select OrderID from dbo.tblEX2 group by OrderID having count(*) = 
        sum(CASE WHEN OrderStatus IN ('CA', 'OC', 'AC') THEN 1 ELSE 0 END))

By the way, it's easier to help if you can provide sample data in the structure I wrote in my first post, so there's a tip for the future

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-28 : 07:37:09

Hey Ryan,

Thanks for the tip and your help.

Is it not just a case of changing MIN to MAX in the Having line?

I'd rather make the smallest changes possible then write a new query if possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 08:03:55

[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]

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-28 : 08:05:03

Thanks visakh16

rugby_fan
Starting Member

21 Posts

Posted - 2008-07-28 : 08:26:10

Hi visakh16,

I made a mistake when i initially described what i needed. Sorry...

I need the below query to update the Orderstatus to 'AP' in the tblEX1 table when NOT all the rows in tblEX2 are equal to ('CA', 'OC', 'AC')

I.E the query will always update tblEx1 Orderstatus to 'AP' except when all rows in tblEX2 are equal to either ('CA', 'OC', 'AC') and have orderID = 101

sorry for the confusion and thanks for the help


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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 08:28:18

[code]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 NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources