What is the best way in SQL to swap the direction of a one-to-many relationship?

Question

Okay, so for whatever reason I have ended up with a situation where the key is pointing the wrong way in a one-to-many. It was obviously never used as a one-to-many, only as a one-to-one, and now there is a need to expand one of those to be many, and the way the key was stored this turned out to be backwards.

The images table has a target_id, target_type and target_column, three pieces of information which identify it with any number of content tables. The target_type just references the table name of the piece of content that is associated to the image. target_column is the name of a virtual column (not actually in the content table) that is used to look up the image. This enables any arbitrary piece of content to have several associated images, each by a different name.

When you have a piece of content and want to find what image is associated to a particular name, you do a

select * from images where target_id = content.id 
    and target_type = "content" 
    and target_column = "image";

All of these pieces of information are available when you have a reference to a particular piece of content.

What I want to do instead is REVERSE all of these, so that the images table knows nothing about the particular pieces of content that reference it, and instead that burden is carried by each of the content tables.

So far I know I can add a column to the content table and then select the information I need from the images table:

select id, target_id from images where target_type = "content";

What I want to do is use this as a subquery and do a mass update of the content table. Is something like this possible?

update content set image_id = 
    (select id from images where target_type = "content") as image_ids where id =
    (select target_id from images where target_type = "content") as content_ids;

I know this fails, but I want to do some kind of mass assignment of target_ids back to image_ids. Is this madness? How do I do this?

Answer 1

You can join in the images table to do the update:

update content inner join images on images.target_id = content.id and 
    images.target_type = 'content' and images.target_column = 'images'
set content.image_id = images.id

Answer 2

Hello,

you might want to use the Mysql multiple-table update mechanism. (cf http://dev.mysql.com/doc/refman/5.0/en/update.html)

in your case, this would be

update
    content,
    images
set
    content.image_id = images.id
where
    images.target_id = content.id
    and images.target_type = 'content'
    and images.target_column = 'images'

I hope this will help you

Jerome Wagner