Dao of Izzy

Updating PostgreSQL fields using multiple tables

// April 29th, 2011 // Digital Walkabout

I was involved in a small project where I had to crawl through several systems to mine data, organize it, and filter it down so it could be placed into a database. The actual crawl took many days to complete and unfortunately, a small irregularity occurred during the process. Simply put, INSERTs were being used when in fact UPDATEs with a check should have been implemented.

Imagine my dismay when I found that, although the information was all in the database, it was floating around jumbled and would be difficult to re-organize, so much so that I almost feared that the a re-crawl could be necessary.

Of course, that would have been insane; after all, once you have your data in a database, there should always be some fancy, efficient way of re-organizing it.

At one point during the process, I needed to able to take data from one field and assign it to a similar field in a different table. To do so I required an UPDATE statement of a caliber I had never used (or needed) before.

At first I tried funny (and terrible) variations like:

UPDATE table_a AS a, table_b AS b SET a.this_field = a.thatfield WHERE a.some_id = b.some_id

I’m sure you’re laughing if you can see I was under the delusion that I could use an UPDATE statement like a normal SELECT statement.

Anyhow, after doing a little digging the solution is still elegant and simple simply requiring something still closely resembling an INSERT query.

I ended up using this format instead:

UPDATE private."MyData" MD SET thisid = C.thisid FROM private."ComparisonData" C WHERE MD.thatid = C.thatid AND (other statements..)

As you can see I’m using a Schema called private which you can happily ignore, and some of the tables and fields’ names have been changed to protect the innocent (or perhaps in this case, the guilty