how to update two tables in a sigle Update Query

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to update two tables in a sigle Update Query

Author

Topic

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2011-10-15 : 11:38:19

Hi,
I have two table,with very minimum difference,i want to update both the tables using a single update query.
Table1 and table2
Please Suggest

Ehtesham Siddiqui
Software Engineer
India

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-15 : 12:02:59

can you post table structures with sample data and expected output?

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-15 : 12:03:00

can you post table structures with sample data and expected output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-15 : 12:42:36

Are you using SQL 2008?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-16 : 09:51:11

you can use dynamic sql query

example

declare @dd nvarchar(1000)
declare @grouptest nvarchar(50)
set @tablename= 'grouptest'
set @dd = 'update ' + @tablename + ' set name =''kk'' where id=5'

exec sp_executesql @dd

you can run @dd once using tablename1 and tablename2
in the variable @tablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 13:53:47

quote:
Originally posted by paultech

you can use dynamic sql query

example

declare @dd nvarchar(1000)
declare @grouptest nvarchar(50)
set @tablename= 'grouptest'
set @dd = 'update ' + @tablename + ' set name =''kk'' where id=5'

exec sp_executesql @dd

you can run @dd once using tablename1 and tablename2
in the variable @tablename

good luck

paul Tech

how will above represent
updating both the tables using a single update query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-16 : 14:46:12

quote:
Originally posted by paultech

set @tablename= 'grouptest'
set @dd = 'update ' + @tablename + ' set name =''kk'' where id=5'

exec sp_executesql @dd

That's almost a textbook example of a SQL injection vulnerability.

--
Gail Shaw
SQL Server MVP

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-17 : 01:40:59

hi,

you can create a layer upon you two table i.e. create a single view on both the table. Then update that view. This will update your underlining tables

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 01:52:51

quote:
Originally posted by jassi.singh

hi,

you can create a layer upon you two table i.e. create a single view on both the table. Then update that view. This will update your underlining tables

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

wont work if you want to updates column from both tables simultaneously.
see bol explanation below

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-17 : 07:40:36

So, how do i mark an answer NOT accepted?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 07:54:04

quote:
Originally posted by DonAtWork

So, how do i mark an answer NOT accepted?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

probably mail him on the same

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-21 : 03:23:30

quote:
Originally posted by Ehtesham Siddiqui

Hi,
I have two table,with very minimum difference,i want to update both the tables using a single update query.
Table1 and table2
Please Suggest

Ehtesham Siddiqui
Software Engineer
India

Read about OUTPUT clause in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources