Solved

Update table 2 from table 1 on ID match

Posted on 2016-07-20
17
17 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
  • 7
  • 6
  • 4
17 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
<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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
> 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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 142

Expert Comment

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

Author Comment

by:Devildib
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
Please consider the above as logic only.Syntax is not good at all
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
Comment Utility
@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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now