File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Multi-Record Update Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Multi-Record Update" Watch "Multi-Record Update" New topic

Multi-Record Update

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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?


SCJP Tipline, etc.
Padma Lalwani
Ranch Hand

Joined: Nov 02, 2004
Posts: 49
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

Joined: Dec 15, 2005
Posts: 333

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.
I agree. Here's the link:
subject: Multi-Record Update
It's not a secret anymore!