Updating data from associations

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Updating data from associations

Post by GeXus »

I'm working on a database that basically used uuid's as the primary keys. We're switching to int(11) as the primary key's with auto incremented tables. Now, we have to re associate the foreign key's to use the new id's as oppose to the uuid's.

Now, all tables have been updated, with the old id columns, renamed to uuid, and added id column as int. In foreign tables, we have records such as user_id, campaign_id, this is the uuid. Now we need to select the new id's from the tables, where user_id = users.uuid, etc. and update associated tables.

Problem is we can't do UPDATE ... SELECT... any idea how we would do this? In a nut shell, we're replacing all our primary id types, and need to re-associate the data based on the old id's.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Updating data from associations

Post by John Cartwright »

Assuming your working on a non-live version of the database, I would simply remove the foreign key constraint, make the neccesary changes to your data, then reinstate the foreign key.
Post Reply