Solved

Update table 2 from table 1 on ID match

Posted on 2016-07-20
17
20 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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 143

Accepted Solution

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

690 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