Need quick help with a SQL query

[brikis98]

It has been years since I've had to do anything more complicated with SQL than your basic SELECT query, so I can't quite recall the best way to do this. We are using PostgreSQL and Java.

I have a HashMap with a bunch of (id, value) pairs. Conveniently, I also have a table with the columns "id" and "value". I need a way to update all those columns in the table with the values from the HashMap. However, this will potentially involve changing millions of rows, so running a million separate UPDATE statements seems like a bad idea. Is there an efficient way to do a single, huge UPDATE statement that will set all the (id, value) pairs properly?

 

[Tea Bag]

If you're using standard JDBC, before any of your database work set the autoCommit property to false:

http://java.sun.com/javase/6/d...setAutoCommit(boolean)

Just remember to commit your transaction when you've done your updates.

Most transactional frameworks expose this to you if you're not using straight JDBC as well, check their docs.

 

[BoberFett]

I'm not familiar with Postgres (wish I was) but if you're truly doing millions of rows at a time you may want to consider a bulk insert. It appears that Postgres has something like that in their COPY function.

http://www.postgresql.org/docs...eractive/sql-copy.html

 

[brandonbull]

if possible, make a backup of "table" then do the update

update table a
set value = (select b.value from HashMap b where a.id = b.id)