?
Solved

Update table 2 from table 1 on ID match

Posted on 2016-07-20
17
Medium Priority
?
21 Views
Last Modified: 2016-07-20
Hi Experts,

I have a sql server table say test1 with 20-25 columns.
I have another table test2 with say 25-30 columns.
There are around 20 matching columns between the two.
Now I want to update test2 from test1 data (only matching columns need to be updated) based upon the ID column which is common to both tables.
I need a generic query where the need to specify column names is not there.

Thanks!!
0
Comment
Question by:Devildib
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41721854
<air code>
UPDATE t2
SET t2.goo = t1.goo, t2.foo = t1.foo, t2.boo = t1.boo -- you get the idea.. 
FROM test2 t2
   JOIN test1 t1 ON t2.ID = t1.ID

Open in new window

0
 

Author Comment

by:Devildib
ID: 41721858
Thanks Jimbo for the quick turn around.But, I am looking for a generic approach where the need to specify column names is not there
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41721861
> where the need to specify column names is not there
Give us more details on what you mean, as SQL Server does not have any functionality to update 'any column not matching between two tables' without explicitly defining them, so the answer is very likely 'You can't do that'.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41721865
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41721868
sorr,y, i got your restriction now.


the answer is: not possible with simple sql.
dynamic sql is what you would need.
usually i would not go that way unless this is to be repeated, then i would codethis into stored procedures...
1
 

Author Comment

by:Devildib
ID: 41721869
Any looping that can be done to check for column names matching and then proceeding with appending the column equations?
Let me give u an example:
@sql = 'update test2 set'
loop starts
check for matching column names between two tables
for each match >> append something like test2.col1 = test1.col1 and keep appending it to the sql syntax above
and finally append the where condition or the join on the ID

POSSIBLE?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41721872
Possible, yes, but it's going to take a pretty long time to write the custom code to pull that off, and to catch every scenario that would prevent it from  executing successfully without error.
0
 

Author Comment

by:Devildib
ID: 41721880
but once done, we need not worry for any column additions or deletions made to any of the two tables.Simple column match logic and updations.If it is possible please help me with some sample code for any 2 small tables for a match on ID column
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41721884
how would you define the joining/where conditions?
0
 

Author Comment

by:Devildib
ID: 41721887
Whats the issue with the where condition? Once you are done looping until the last column name matching, you stop there.What you have until at this point is a statement similar to:
update test2 set test2.col1 = test1.col1,test2.col2 = test1.col2..........

 and simply apply the where condition at the end of it
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41721893
not clear to me, and i consider myself very good in sql

so please show with some examples how you "see" this where clause. example tables, " before" and "after" data or the sql you want to be build.
the looping will surely and indeed not be a problem
https://www.experts-exchange.com/articles/13640/processing-cursor-vs-temp-table-syntax.html
0
 

Author Comment

by:Devildib
ID: 41721914
I am sorry for being unclear. I regard myself very poor in SQL. So I am here.
let me provide you my best thoughts in an algorithm good to understand:

function or stored proc starts
@TMdot = 'test1.'
@TDdot = 'test2.'
@s = 'update test2 set '
@MyTableDef --variable to hold test1 column names in loop
@cnt = count of columns in test1 --setting an upperlimit for the loop
For j = 0 To @cnt - 1
 @n = Trim(MyTableDef(j).Name)
  @s = @s ||  TMdot || @n || = || TDdot  || @n  If j < cnt - 1 Then
    s$ = s$ & ","
  Else

  @s = @s ||  from test2,test1
  @s = @s || ' where  TDdot  ||ID = TMdot ||ID  End If
         
next
0
 

Author Comment

by:Devildib
ID: 41721915
Please consider the above as logic only.Syntax is not good at all
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41721919
>I regard myself very poor in SQL.
In that case I highly recommend against trying to pull this off, as whatever solution we give you will have to be supported when things change, and any resulting script will be long and complex, therefore I humbly propose that you will not be in a good position to do that.

So ... I HIGHLY ( HIGH - LEE ) recommend explicitly spelling out the columns.
0
 

Author Comment

by:Devildib
ID: 41721924
@Guy Hengel  : Sir my comment was a reply to your comment.You asked me where do I see the "WHERE" condition going, and I tried to answer.

@Jimbo : Sir, Please have faith.I for got to tell you that I am a fast learner. :)
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41721930
i dont want pseudo sql
i want you to spell out a real example
note that you will need to clarify how to match the columns
as if you think to do the match on the name, the Id column names will likely also match.
so spell out the rules ( in english, not in pseudo sql)

to list columns of a table, use syscolumns system view
you can join thst view to match column names from the two tables
you can insert that into a table variable, and use my above link to loop on that one to apoend to the @sql variable
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41721932
so i see the "issue" on how you want to distinguish between those columns that are to be put into the Set, and those for the where...
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

762 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