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 |
|
d3ng
Yak Posting Veteran
83 Posts |
 Posted - 2007-07-11 : 01:46:10
|
| hi experts. I would like to ask on how to update many fields in one execution. I want to manually update the mark up price of the items table from 40% to 30% markup, but the problem is there so many sale price fields to update. I want to create a script that will update all the sale price. Pls see table structure(Table structure)Table name: Itemsfields: Itemdesc, itemid, saleprice1, saleprice2, saleprice3..Untile saleprice80Sample script:(first script)update items set saleprice1 = purcprice + purcprice * .30 where itemid = '0000000015'update items set saleprice2 = purcprice + purcprice * .30 where itemid = '0000000015'update items set saleprice3 = purcprice + purcprice * .30 where itemid = '0000000015'(Revised script)update items set (Select COLUMN_NAME from myDatabase.INFORMATION_SCHEMA.COLUMNS where table_name = 'items' and column_name like '%SALEPRICE%') = purcprice where itemid = '0000000015'....my revised script view this error.Error Msg:Msg 102, Level 15, State 1, Line 2Incorrect syntax near '('.Msg 102, Level 15, State 1, Line 3Incorrect syntax near '='.Darren Bernabe Blanco |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-07-11 : 01:54:13
|
You can't do that. You can't use have dynamic column name in there. You have to use Dynamic SQL to do it. see http://www.sommarskog.se/dynamic_sql.html
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
svicky9
Posting Yak Master
232 Posts |
Posted - 2007-07-11 : 10:34:46
|
| Get the Column names in a cursor and within the Cursor write the Dynamic query for Update.Vichttp://vicdba.blogspot.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-11 : 10:55:36
|
| what i'd like to know is, where did you get the idea that this would even work, d3ng?i'm curios of the way of thinking._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
|
|
Update fields