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 |
|
chriskhan2000
Aged Yak Warrior
544 Posts |
 Posted - 2005-03-15 : 17:08:06
|
| Is it possible to update a whole bunch of fields from one table all at once with 1 update query? ExampleUPDATE PARTSET buyer_user_id = NULL, planner_user_id = NULL, order_point_quantity = NULL, order_policy = 'Discreet', user_defined10 = NULLWHERE (buyer_user_id = 'MJ' or buyer_user_id = 'MWJ') OR(planner_user_id = 'MJ' or planner_user_id = 'MWJ') AND (order_point_quantity <> '') and (order_policy <> 'Descreet') AND(user_defined10 <> '')So here's what the update is going to do:1. Update field planner_user_id or buyer_user_id that equals to 'MJ' or 'MWJ' to blank. **only one field has the field filled in.2. If order_point_quantity field is not blank and tied to 'MJ' or 'MWJ' then make it blank.3. If order_policy field is other value, change it to 'Discreet' if it is related to planner_user_id or buyer_user_id 'MJ' or 'MWJ'.4. Finally cleared the user_defined10 field if it is not cleared already with the condition of planner_user_id or buyer_user_id 'MJ' or 'MWJ'.Hopefully this gives you guys enough information to see if it's possible to do this and also if my update query looks right. I don't want to do a mass change unless I can get a feeling that my query is correct.Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-15 : 17:47:46
|
| I don't think that you want to do this in one update statement based on your description. It seems that the WHERE statement will UPDATE more rows that are needed as you are adding in all possible logic for each of the 4 items. You should have one UPDATE statement for each grouping of updates. So if you want to update two columns WHERE Column1 = '' OR Column2 = '', then update those two columns in one statement. Now let's say you want to update two other columns WHERE Column1 = 'A' OR Column2 = 'B', then do that in a separate UPDATE statement. Don't combine them just because you need to do two updates.You should be testing this on a test database rather than wondering if this will work. Even if we say yes, I still would not recommend running this on live data.And in order for us to help you, we'd need DDL for your table, sample data that shows your problem, and the expected result set using that sample data.Tara |
 |
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-03-16 : 08:51:47
|
| Thanks for the advise. I will test it out and see how it works first. Will probably do separate updates then. |
|
|
|
|
|
|
Update several fields with one update query?