Building an SQL query to update a bunch of tables using data from each row of a separate table

Benjamin Hall

I have a mapping table db1.a_to_b which maps between db1.a.id and db2.b.id. Which is rather stupid, since really db2.b.id and db1.a.id should simply be synonyms. Then we have a bunch of tables db2.c through db2.z (4-5 of them) which have foreign keys to db2.b.id.

The goal is to completely remove db2.b as being redundant and simply index all of those tables based on an opaque id (which happens to be identical to db1.a.id, but nothing in the code should care). Basically this is a client ID. b had a point in the initial design (to let us use more than just client ids), but that part of the design got obviated real quick and the domain doesn't really support more granular "clients" than the top level ones for this purpose.

Moving forward, that's pretty easy. But there's existing data which has the "bad" b.id values (both auto-increment, but starting from vastly different points and with deletions on both sides....)

So now I'm trying to see if there's a clean way of doing the data surgery to update every case where db2.b.id = 1 (for instance) to the other side of the db1.a_to_b mapping table (say 42).

Options:

In <favorite programming language>, pull the a_to_b table and store those mappings. Loop over that data set and for each element, do UPDATE db2.c SET ... WHERE ... = ... for each of the affected tables in a separate transaction.
In <favorite programming language>, pull the mappings and synthesize the full set of SQL statements, apply them all at once (basically doing the same thing, just in one transaction for the updates)
Craft some sort of SQL query to do it all automagically.

At this point, I don't expect the total number of rows in each table affected to be super huge; maybe ~100 mapping entries and ~10-50 entries per table per mapping entry. But this would have to run in production (eventually).

Thoughts? DB is an archaic MySQL (5.x) if that matters.

loopback0

I'd do it in SQL. Even MySQL 5.1 supports UPDATE with JOIN.

PleegWat

@loopback0 said in Building an SQL query to update a bunch of tables using data from each row of a separate table:

I'd do it in SQL. Even MySQL 5.1 supports UPDATE with JOIN.

This.