Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi All,
 
I want to update multiple columns from multiple tables in a single UPDATE Query...
Just want to do like below query...
UPDATE Table1, Table2
SET Table1.Column1 = 'one' 
,Table2.Column2 = 'two'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = 'id1'
 

Does Sql Server 2008 provide any mechanism to do so?
 
If Sql Server 2008 provide such nice approach, please share some links with me!
 
Thanks!
Posted 8-Mar-13 3:03am
Edited 8-Mar-13 5:28am
Maciej Los134.4K
v2
Comments
ryanb31 at 8-Mar-13 9:31am
   
Silly question, but have you tried it?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Well, in short - not possible.The same restriction for updateable views:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
 

Approach that I believe is optimal: use transactions, possibly wrap your business logic into stored procedures. Code skeleton might look like below:
 
begin tran
 
<TSQL statement for table 1>
if @@error != 0 goto error
 
<TSQL statement for table 2>
if @@error != 0 goto error
 
commit tran
goto ok
 
error:
 
<error_handler>
rollback tran
 
ok:
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

try using foreign key "on update cascade".
 
see if id's or by any other means primary key- foreign key constraint can be applied
  Permalink  
Comments
Member 8090436 at 11-Mar-13 10:10am
   
Thanks,
Your provided approached helpful only while we need to change Parent column and want to update child column value when they have Primary-foreign key relationship.
But in my scenario assume both belongs to different tables and also different columns.
If you have idea suggest me one or two!
Thanks!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Schatak 394
1 OriginalGriff 355
2 Sergey Alexandrovich Kryukov 119
3 _Amy 115
4 Rob Philpott 100
0 OriginalGriff 7,097
1 Sergey Alexandrovich Kryukov 5,623
2 Maciej Los 3,504
3 Peter Leow 3,373
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 8 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100