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

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?

---

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

---

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