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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-11-26 : 10:07:38
|
| Hi. I'm trying to update a key field -- I'll call it my anchor field -- in a table using the value in Field1 (see below) in the same table, ONLY after many criteria are met. I have tried writing an UPDATE statement, which seems to work, but the odd thing is that I KNOW there are only a handful of updates to be made based on the many criteria I have established in the WHERE clause, and my UPDATE statement is listing over 200 records "affected" in Enterprise Manager. When I check the records, the update seems to have worked, but, again, if SQL is telling me over 200 records have been affected, I know something is up. BTW, I'm testing this on a copy of the production table in order to iron out the bugs first.The idea here is that Anchor_field should only be updated with Field1 values when the conditions in the following update statement are met:UPDATE MAIN_TABLESET MAIN_TABLE.ANCHOR_FIELD = MAIN_TABLE.FIELD1WHERE MAIN_TABLE.FIELD1 IS NOT NULL AND MAIN_TABLE.FIELD1 <> '0' AND COALESCE (MAIN_TABLE.FIELD2, MAIN_TABLE.FIELD3) IS NULL OR COALESCE (MAIN_TABLE.FIELD2, MAIN_TABLE.FIELD3) = FIELD1 AND MAIN_TABLE.ANCHOR_FIELD = '0' OR MAIN_TABLE.ANCHOR_FIELD IS NULLHere's an example of data in the table, followed by notes on what SHOULD happen if the UPDATE statement is written properly: Ex. 1 Anchor F1 F2 F3 0 <NULL> 2 3*in Ex. 1 above, anchor_field should NOT be updated with field1 value Ex. 2Anchor F1 F2 F3 0 1 1 1*in Ex. 2 above, anchor_field SHOULD be updated with field1 value Ex. 3Anchor F1 F2 F3 <NULL> 2 <NULL> <NULL>*in Ex. 3 above, anchor_field SHOULD be updated with field1 valueAny help is appreciated. I want to make sure I'm not doing an inadvertent updating! Also, I sense that the order of my key word operators, NOT NULL, AND, OR may be flaky. Any quick rule on the proper order of operators?Edited by - steelkilt on 11/26/2002 10:09:14 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-26 : 10:10:49
|
| Because of your lack of brackets around the different parts of the conditions I would imagine thatORCOALESCE (MAIN_TABLE.FIELD2, MAIN_TABLE.FIELD3) = FIELD1 Is being evaluated as the only necessary matching condition. Bracket it in with whatever AND condition it should be associated with.In fact you have an even worse one belowOR MAIN_TABLE.ANCHOR_FIELD IS NULL So your update will update any record where MAIN_TABLE.ANCHOR_FIELD IS NULL-------Moo. |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-26 : 10:17:26
|
| To make that more obviouscreate table #moo(thing int null)insert into #moo values (1)insert into #moo values (2)insert into #moo values (3)SELECT * from #moo where thing = 1 and thing = 3 or 1 = 1--This returns everything because 1 is always equal to one.select * from #moo wherething = 1 and (thing = 3 or 1 = 1)--This returns just the row which matches thing = 1 AND (that either thing = 3 or 1 = 1)drop table #moo-------Moo. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-11-26 : 10:47:16
|
| Thanks for the tips. Can someone point me to a good resource that will show me actual examples of complex 'NOT/AND/OR' updates so I can see bracketed expressions in action?thx |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-26 : 11:03:39
|
| Books on line has its own section on AND operatorsIf you search for AND operators and then go to"Rules and Grid-Pane Conventions for Combining Search Conditions"It explains them there.-------Moo. |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
|
|
|
|
|
|
|
Runaway UPDATE statement -- pls. help