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.
Author |
Topic |
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2007-09-11 : 13:20:39
|
OK we did something wrong..I need to update a Date with two criteria, here is the statement.Update extensions set inactivedate = '01/01/2004' where exists(select * from extensions where siteid=2 and lastactive is null and active =1)when this was exicuted it updated ALL of the dates to 01/01/2004, not just the ones that were siteid=2 and null in lastactive.where did the statement fail?thanks |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-11 : 13:24:52
|
there is no relationship in the exists subquery and your table extensions...you should be better off with an IN or join next timemy sympathy goes to you... had this happen to me before --------------------keeping it simple... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 13:26:28
|
When you decided to NOT include a correlated value in the subquery. E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 13:27:57
|
There is no need for a subquery here at all.Update extensions set inactivedate = '01/01/2004'where siteid=2 and lastactive is null and active =1 E 12°55'05.25"N 56°04'39.16" |
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2007-09-12 : 11:21:38
|
Well it only changed about 12,000 rows, I am glad that we had a recent backup.....thanks I will run the query a little latter, |
|
|
|
|
|