UPDATE - 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

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-11-17 : 07:27:37

i have this query:

UPDATE FROM OrdersTable
SET TotHour=TotHour-12
WHERE OpenDate<='2009-02-08' And CloseDate>='2009-02-08'

UPDATE FROM OrdersTable
SET TotHour=TotHour-12
WHERE OpenDate<='2009-02-09' And CloseDate>='2009-02-09'

UPDATE FROM OrdersTable
SET TotHour=TotHour-12
WHERE OpenDate<='2009-02-10' And CloseDate>='2009-02-10'
....

how can i use table that hold the dates,instead to write same statments?
i have a lot of this statments ,but anothers dates

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 07:29:32

DELETE and SET together looks senseless...

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-11-17 : 07:55:52

thanks webfred ,i change it to UPDATE

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 08:05:40

Do you mean this?

UPDATE OrdersTable
SET TotHour=TotHour-12
WHERE
(OpenDate<='2009-02-08' And CloseDate>='2009-02-08')
OR
(OpenDate<='2009-02-09' And CloseDate>='2009-02-09')
OR
(OpenDate<='2009-02-10' And CloseDate>='2009-02-10')

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-11-17 : 08:26:16

yes,but i want to hold that dates in a table how can i use this table in the query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 10:27:41

Test it please:
update o
set TotHour=TotHour-12
from OrdersTable o
join dates_table dt on (o.OpenDate<=dt.OpenDate And o.CloseDate>=dt.CloseDate)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources