?
Solved

How to update multiples tables with similar values with single update command.

Posted on 2012-08-18
5
Medium Priority
?
317 Views
Last Modified: 2012-08-18
update mstchvs set quarter='P1' where ltrim(rtrim(month)) in('October','November','December') and year=2010

I want to do this similar update from t1,t2,t3,t4


How to achieve this.
0
Comment
Question by:searchsanjaysharma
5 Comments
 
LVL 1

Expert Comment

by:mr-kenny
ID: 38307631
I not understand exactly what to want to do. Do you want to use variables?

DECLARE @i1 INT

SET @i1 = 100

DECLARE @string11 NVARCHAR(50)

SET @string11 = 'MyString'

SELECT * FROM dbo.t1 WHERE dbo.t1.Col1 = @i1
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 500 total points
ID: 38307649
You have to specify 4 update statements. There is no other way. Do NOT wrap the column names in the where clause in any functions. If you do that the engine won't be able to use the index, if there is one and it better be, and the update it could take forever. If the month column is of varchar type the trailing spaces (at the end) are ignored but if you suspect you might have leading spaces (at the beginning) then you will have to clean that up.


update mstchvs set quarter='P1' where month in ('October','November','December') and year=2010

update t1 set quarter='P1' where month in ('October','November','December') and year=2010

update t2 set quarter='P1' where month in ('October','November','December') and year=2010

update t3 set quarter='P1' where month in ('October','November','December') and year=2010

update t4 set quarter='P1' where month in ('October','November','December') and year=2010
0
 
LVL 1

Expert Comment

by:mr-kenny
ID: 38307660
With Dynamic SQL it would be possible to use varibles for tables and columns but I also suggest to use separate Update Statements instead of dynamic Sql.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 38307951
Zberteoc is correct; UPDATE statements are for ONE and only ONE table at a time.
What you could do though is bundle all updates in a transaction so that it is guaranteed that all updates will either succeed or fail together.

BEGIN TRY
  BEGIN TRAN tr
  -- All five UPDATE statmenets go here
  COMMIT TRAN tr
END TRY

BEGIN CATCH
  -- An error occured
  ROLLBACK TRAN tr
END CATCH
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 38308920
This i was already doing it. But i liked comment by jimhorn, Thanks to all.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question