Update statement with multiple tables

Question

Post reply

Update statement with multiple tables

boob 22132

Grasshopper

Points: 14
More actions
June 6, 2010 at 10:29 am

#97047

Hello Everybody,
Can anyone suggest some script how to use the following update statement, may be using a script or something else?
--------------
update[\b] BackupDetails, Material_Master, WB06Material_purchased1011,
WB06Musterrollmaster1011, WB06MustRoll1011,WB06Registration,WB06Work_demand0607
WB06Work_demand1011,WB1011SummaryMust,WBDrought_Proofing,WBexp_amount1011
WBFlood_control,WBMaterial_Master,WBOtherWorks, WBPan_account1011
WBPan_trans1011,WBRC_Stretch_details,WBrecieve_blk1011,WBimpl_account1011
WBrecieve_pan1011,WBRural_Connectivity,WBWater_conservation, WBWork_detail
WBWork_Material,WBWork_Sanction,work_category,work_present_status,
work_proposed_status,work_status,Work_type,wrk_act_master
set updateflag=NULL where updateflat='Y' or updateflag='y'[\b]
A fast solution highly solicited.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you please clarify your requirement?

Are all those elements in your list columns, tables or something else?

Do you really have two columns [updateflat] and [updateflag] or is it a typo and you're trying to cover upper and lower 'y'?

If so, what is the collation of your target table(s)? Case sensitive or insensitive?

Right now there is not enough information to help you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul_ramster Ten Centuries Points: 1382 More actions · Answer 2

I think you want something like this (you'll need to complete the list of tables, I only included the first few for the sake of brevity):

exec sp_MSForEachTable

@command1 = 'update ? set updateflag = Null where upper(updateflag)=''Y'''

,@whereand = 'and name in (''BackupDetails'',''Material_Master'',''WB06Material_purchased1011'',''WB06Musterrollmaster1011'',''WB06MustRoll1011'')'

Be careful with sp_MSForEachTable, though. You can break things with it!

boob 22132 Grasshopper Points: 14 More actions · Answer 3

>> the col is 'updateflag'.

>> These are all tables:

BackupDetails, Material_Master, WB06Material_purchased1011,

WB06Musterrollmaster1011, WB06MustRoll1011,WB06Registration,WB06Work_demand0607

WB06Work_demand1011,WB1011SummaryMust,WBDrought_Proofing,WBexp_amount1011

WBFlood_control,WBMaterial_Master,WBOtherWorks, WBPan_account1011

WBPan_trans1011,WBRC_Stretch_details,WBrecieve_blk1011,WBimpl_account1011

WBrecieve_pan1011,WBRural_Connectivity,WBWater_conservation, WBWork_detail

WBWork_Material,WBWork_Sanction,work_category,work_present_status,

work_proposed_status,work_status,Work_type,wrk_act_master.

>> I wish to set the updateflag to NULL just by writing one script and not buy writing 30 update statements.

=============

For the second suggestion:

is it correct to write

exec sp_MSForEachTable

@command1 = 'update ? set updateflag = Null where

upper(updateflag)=''Y''' or

lower(updateflag)="y'"'

@whereand = 'and name in

(''BackupDetails'',''Material_Master'',''WB06Material_purchased1011'',''WB06Musterrollmaster1011'',''WB06MustRoll1011''.............)'

will it update all the table just by running that script?

Please tell me the purpose of sp_MSForEachTable

AND

@whereand keyword.

Thank U.

paul_ramster Ten Centuries Points: 1382 More actions · Answer 4

sp_MSForEachTable is an undocumented system stored procedure. Althought the official Microsoft documentation does not include any details on it, there is much information available on the 'net: http://www.google.com/search?hl=en&q=sql+server+sp_msforeachtable.

It runs a query against the system metadata to generate a list of tables. For each table found, the "?" in @Command1 is replaced by the tablename when the command is run.

@Whereand is an additional clause that can be used to filter the list of tables.

If you run the command as I showed above it will do what you want on the list of tables I entered. You will just need to complete the table list as previously suggested.