How To Make Update Statements From phpMyAdmin
I figured out this cool trick today: how to make update statements straight from phpMyAdmin. The normal way I go about this is either to write them using php or to do them using regular expressions in Notepad++. However, both of those can be kind of time consuming.
Today I needed to update one table with values from another table (replacing a last name with an id). Here is a simple version of how the tables are laid out:
Table 1 (counties)
id | person | county
Table 2 (people)
id | lastname | firstname | address
Table 1 had the person’s last name in the `person` field and that needed to be changed to the `id` of the person (in case the last name changed). To do that, I did a select statement with a concat() function in phpMyAdmin:
SELECT concat( ‘UPDATE counties SET person=\”, id, ‘\’ WHERE person LIKE \”, lastname, ‘\’;’ ) FROM `people`
The output from that statement created the all of the update statements I needed and I copied and pasted those right into the sql input box. Wha-la, almost instant update statements with no php middle man.
I think you can also do this with update statement using a join to automatically update one table from another, but I didn’t feel like doing a Google search 
Maybe that will be another post, but if anyone cares to comment on that, that would be cool.
Subscribe with RSSCategory: Programming | 388 views | Posted: January 4th 2008 05:16 pm
No comments yet.