Search code examples
mysqlmyisam

Change a field across many MySQL tables

Scenario: I have users that want to be able to change their username (unfortunately I didn't think about adding this functionality when I started the site), but the MySQL database has 110 tables.

Am I going to have to just make a script that executes 110 queries to update the username in each table (and remember to update the script when I add new tables), or is there a way to link the fields in all of the tables, so updating the username in one table updates it in all of the others?

Solution

  • You can setup the tables to do CASCADING updates.

    This is accomplished by using foreign keys and the ON UPDATE CASCADE

    Here is a good article on it: http://www.oreillynet.com/onlamp/blog/2004/10/hey_sql_fans_check_out_foreign.html

    And the official MySQL page: http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

    UNFORTUNATELY this is for InnoDB only, so you would have to switch the tables over.