update multiple records with condition

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

SQL Server 2005 Forums

Transact-SQL (2005)

update multiple records with condition

Author

Topic

tech1
Starting Member

49 Posts

Posted - 2010-06-16 : 04:56:00

I obviously know how to update records BUT I need to update a record where an OrderID matches the currentDate - but there can be multiple records returned because there maybe multiple records where the currentDate is true.

quote:

SELECT [orderId] FROM OrderFeedback
WHERE DATEADD(dd, 0, DATEDIFF(DD, 0, FollowUpDate)) =
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE()))

current attempted query:

quote:

UPDATE OrderItem
SET orderStatusId = 4 /* Pending Feedback */
WHERE orderId = (

SELECT [orderId] FROM OrderFeedback
WHERE DATEADD(dd, 0, DATEDIFF(DD, 0, FollowUpDate)) =
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE())) )
AND orderStatusId = 3

the subquery can return more than 1 record - for each record in the subquery, I want to execute the parent query for the multiple records that return back

makes sense?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 05:09:10

This?


UPDATE OrderItem
SET orderStatusId = 4 /* Pending Feedback */
WHERE orderId in (

SELECT [orderId] FROM OrderFeedback 
WHERE DATEADD(dd, 0, DATEDIFF(DD, 0, FollowUpDate)) = 
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE())) )
AND orderStatusId = 3

No, you're never too old to Yak'n'Roll if you're too young to die.

tech1
Starting Member

49 Posts

Posted - 2010-06-16 : 05:13:19

funny, 10 mins before you posted I just figured that one out!

awesome, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources