query updated 8000 rows instead of 1

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

query updated 8000 rows instead of 1

Author

Topic

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 07:36:09

hey guys and help.

I ran this query on my table and it should have only updated one row but it updated every single row in my database.

UPDATE    x
SET              x.OrderStatus = 'AP'
FROM         dbo.tblEx1 AS x LEFT JOIN
                          (SELECT     OrderID
                            FROM          dbo.tblEx2
                            WHERE      OrderID = 10055
                            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

2 things.....how do i undo this?

and why did it happen in the first place, There is only one orw with orderID = 10055.

thanks for any help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 07:45:55

Undo?
Don't know. Do you have not saved?

Why?
WHERE y.OrderID IS NULL

Webfred

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 08:18:04

thanks webfred. so if i take out the WHERE y.OrderID IS NULL the query should work fine?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 08:22:26

NO!
But you can try
WHERE y.OrderID IS NOT NULL

because you want only matching records and left join takes all records and gives null for each joined row when it's not matching.

Webfred
PS: Save your Table before testing!

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 08:26:53

thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources