Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multi-Record Update

 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all - a friend of mine has run across a situation and we can't seem to find a good SQL statement to handle it. Basically, we want to update multiple records in one table with data from another table. This is what we have:



To the best of my knowledge, Table B is a "temp" table that is being created by using DTS (SQL Server) to import a CVS file. Really what we want to do is merge this data with the records in Table A. We need to find the records in which the RecordID matches from Table A to Table B and, for each of those cases, copy the FilePath from TableB.FilePath to TableA.FilePath.

Unfortunately, I'm at a bit of a loss on how to do this. Anyone have any ideas?

My suggestion, which I admit is a bit ugly, is to do something like this:

1. Do a join between Table A and Table B where RecordID matches, keeping everything from Table A except for FilePath, which we keep from Table B. Store this in a temporary table.

2. Remove all records from Table A in which the RecordID exists in the temporary table we created in step 1.

3. Do a mass insert of everything in the temporary table we created in step 1 into Table A.

4. Delete the temporary tables.



I don't really like the idea of constantly deleting large chunks of data and moving things around like this. Anyone else have any better ideas?

Thanks,
Corey
 
Padma Lalwani
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you not just update the matching records? Why delete and insert?


update a
set a.filepath=b.filepath
from a,b
where a.recordid=b.recordid
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


That final WHERE clause is optional, depends on whether you want to overwrite existing values or not. Actually, you probably don't and that bit should be removed from the SQL.

This will run considerably faster if RECORDID is a primary key or an indexed column. It absolutely depends on the uniqueness of RECORDID in table B.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic