I need help with updating multiple columns in a table
-
Hi,
I need to update Multiple columns in one table with corresponding data from another table.
example
Table A
Col1
Col2
Col3
Col4
Col5
Table B
ColA
ColB
ColC
ColD
ColE
need to update TableA.Col3,TableA.Col4,TableA.Col5
with Data From TableB.ColC,TableB.ColD,TableB.ColE
Where TableB.ColA = TableA.Col1
Thanks you very much for your time and help
j
-
This should do the trick.
UPDATE TableA
SET Col3 = (select ColC from TableB t2 inner join TableA t1 on t1.Col1 = t2.ColA where ColA = TableA.Col1),
Col4 = (select ColD from TableB t2 inner join TableA t1 on t1.Col1 = t2.ColA where ColA = TableA.Col1),
Col5 = (select ColE from TableB t2 inner join TableA t1 on t1.Col1 = t2.ColA where ColA = TableA.Col1)
where Col1 in(Select ColA from TableB)
-
jpack23,
I previously posted a solution; however, I had a mistake in it. I had forgotten to put a where clause in the subqueries, thus the result set would throw an eror because more than one value could be returned. I was rushing and did not test the solution properly. I have edited the post with the correct soution and the test script used.
Adam
create table #TableA
(
Col1 int,
Col2 char(10),
Col3 char(10),
Col4 char(10),
Col5 char(10)
)
create table #TableB
(
ColA int,
ColB char(10),
ColC char(10),
ColD char(10),
ColE char(10)
)
insert into #TableA Values (1,'a','abc','def','ghi')
insert into #TableA values (2, 'b','test','test','test')
insert into #TableA Values (3, 'c', 'test2','test2','test2')
insert into #TableB Values (1,'a', 'stu','vwx', 'zzz')
insert into #TableB Values (2,'b','jkl','mno','pqr')
UPDATE #TableA
SET Col3 = (select ColC from #TableB t2 inner join #TableA t1 on t1.Col1 = t2.ColA where ColA = #TableA.Col1),
Col4 = (select ColD from #TableB t2 inner join #TableA t1 on t1.Col1 = t2.ColA where ColA = #TableA.Col1),
Col5 = (select ColE from #TableB t2 inner join #TableA t1 on t1.Col1 = t2.ColA where ColA = #TableA.Col1)
where Col1 in(Select ColA from #TableB)
select * from #TableA
Select * from #TableB
drop table #TableA
drop Table #TableB
-
I think the most efficient way is to use inner join when updating value from another table:
Update Table1 Set Col1 = Table2.Col1, Col2 = Table2.Col2 ...
From Table1 Inner Join Table2 ON Table1.Key = Table2.Key
Where ...
-
I have to agree. The inner join seems like the most logical approach.
Using the same temp tables above:
UPDATE #TableA
SET Col3 = b.ColC ,
Col4 = b.ColD,
Col5 = b.ColE
FROM #TableA a
INNER JOIN #TableB b
ON a.Col1 = b.ColA
Clean, simple. What's more, check the query plan from the first answer:
Compare that with the plan from the query above:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Alanz and Grant, I would agree with your solution, the query plans speak for themselves. The proposed solution is much more efficient and logically cleaner.
-
create table #TableA
(
Col1 int,
Col2 char(10),
Col3 char(10),
Col4 char(10),
Col5 char(10)
)
create table #TableB
(
ColA int,
ColB char(10),
ColC char(10),
ColD char(10),
ColE char(10)
)
insert into #TableA Values (1,'a','abc','def','ghi')
insert into #TableA values (2, 'b','test','test','test')
insert into #TableA Values (3, 'c', 'test2','test2','test2')
insert into #TableB Values (1,'a', 'stu','vwx', 'zzz')
insert into #TableB Values (2,'b','jkl','mno','pqr')
/*
UPDATE #TableA
SET Col3 = (select ColC from #TableB t2 inner join #TableA t1 on t1.Col1 = t2.ColA where ColA = #TableA.Col1),
Col4 = (select ColD from #TableB t2 inner join #TableA t1 on t1.Col1 = t2.ColA where ColA = #TableA.Col1),
Col5 = (select ColE from #TableB t2 inner join #TableA t1 on t1.Col1 = t2.ColA where ColA = #TableA.Col1)
where Col1 in(Select ColA from #TableB)
*/
select * from #TableA
select * from #Tableb
Instead of Commented Line Use the Below line with single Join
Update #TableA
set #TableA.Col2 = x.colB,
#TableA.Col3 = x.colC,
#TableA.Col4 = x.colD,
#TableA.Col5 = x.colE
from
(
Select b.ColA, b.ColB, b.ColC, b.ColD, b.ColE from #TableB b
Inner JOin #TableA a on a.Col1 = b.ColA
) x
Where x.COlA = #TableA.Col1
drop table #TableA
drop Table #TableB
-
This isn't Oracle... no need to slow down the update with correlated subqueries like that... just do a simple join like some of the other posters have.
Recommend you lookup UPDATE in Books Online and learn how to use it in SQL Server... it's a LOT different and easier to use than the UPDATE in Oracle and many other RDBMSs.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
In the interest of completion. Here's the execution plan for the latest solution. I'd stick with the simple join statements:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi,
You can update you query in best way like below
Update TableA set a.col3=b.colc,a.col4=ColD,a.col5=ColE from TableA a, TableB b
Where b.ColA = a.Col1
Its similar like the join Query. Try This and check the performance.
Regards
Antony
-
antony (10/15/2007)
Hi,You can update you query in best way like below
Update TableA set a.col3=b.colc,a.col4=ColD,a.col5=ColE from TableA a, TableB b
Where b.ColA = a.Col1
Its similar like the join Query. Try This and check the performance.
Regards
Antony
That works because it's just a non-ansi join. No major differences from the JOIN method above. I'd stick with using ANSI methods though. There are instances where the non-ansi approach will return bad data and/or perform quite a lot slower than the correct INNER/OUTER JOIN ON syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You've seen simple equi-joins go bad? Any chance you have an example of one that has gone bad?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No, not a simple equi-join, no. A simple outer join, yep.
http://www.sqlservercentral.com/articles/Advanced+Querying/outerjoinmystery/2382/
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Whew! Yeah, I knew the *= and =* outer joins would sometimes cough up bad results... they actually started going bad in SQL Server 7... I'm surprised they didn't remove them altogether in 2k.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey Antony,
I execute your SQL Query,
I found 2nos. Error,
1st Error
======
Table name is not Correct,
you have specified TableA instead of #TableA
2nd Error
======
You have used alise name in Set Statement
which is not allowed in SQL Server (I am not sure)
ie. Set a.col3 = b. ColE (Which is showing Error)
And I support to Grant Fritchey Query, He follow the ANSI Join which is the Best Practise of using Join as per Microsoft SQL Server,
If I am wrong, Please Correct Me.
Cheers!
Sandy.
--
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply