« Ice climbing trip | Main | How should the church answer the MySpace revolution? »
February 17, 2007
MySQL hack of the day
I was doing some work on an older version of mysql (3.23.58 to be exact) today, and had to do a multi-table update, which isn't available on that version (at least I couldn't get it to work). Here's the relevant schema:
table contacts
id INT
donor_id INT (foreign key)
...
table donors
id INT
status ENUM ...
...
table temporary_donors_to_contact_ids
donor_id INT
contact_id
I was trying to backfill the donors table, and had to subsequently update the contacts table with the appropriate foreign key from the donor auto_incremented id. If I were on a current version of mysql the query would most likely look like something this: update contacts c, temporary_donors_to_contacts_ids t set contacts.donor_id = t.donor_id where c.id = t.contact_id; # untested, and sheer speculation. I do this type of thing so infrequently, there might be an elegant single query solution with this version of MySQL, but I didn't want to waste too much time since I was on such an old version.
Anyway, I was really pulling my hair out, when I thought of this terribly hacky solution that was so crazy I thought I'd post about it.
I quit mysql and ran this from the command line:
Those of you familiar with mysql, know that when you run a query in the mysql client, it will output the results formatted in a table. When using mysql -e, it doesn't output the pipe delimiters. I was planning to strip those out using the venerable CTRL-V (Visual Block) mode in vim.
It worked so well, I just might use it again in the future -- metaprogramming sql statements ... very hacky, but pretty cool!
Update: I found in the mysql documentation that "Starting with MySQL 4.0.4, you can also perform UPDATE operations covering multiple tables." So, what I was doing wasn't possible with the version I was using.Posted by mark at February 17, 2007 3:39 PM Subscribe (FeedBurner)