I have the following structure in MySQL, table 'images_tags':
id | image_id | tag_id
----------------------
1 | 243 | 52
2 | 94 | 52
3 | 56 | 52
4 | 56 | 53
5 | 56 | 54
Table 'tags':
id | tag
---------------
52 | fashion
53 | cars
54 | sports
55 | bikes
I'm building a function in my CMS to delete a tag, for that I need to reassign all the pictures containing that tag to a another tag. The problem is the picture can already have assigned the new tag and I want to avoid possible duplicated records.
I couldnt find the right way to do it straight in SQL so I tried in PHP as follows:
$result=mysql_query("select image_id from images_tags where tag_id='".$oldtag."'");
while($row=mysql_fetch_assoc($result)){
$result2=mysql_query("select id from images_tags
where image_id='".$row['image_id']."' and tag_id='".$newtag."'");
if(mysql_num_rows($result2)==0){
mysql_query("update images_tags set tag_id='".$newtag."'
where image_id='".$row['image_id']."' and tag_id='".$newtag."'");
}
}
As you can see, my code is very bad and non-efficient as I'm running queries inside iterations. Do you know a better way to do this? Preferably in just one SQL query. Thanks.
When I think of this problem, I think of it more easily as "insert the new image tags, if appropriate, then delete the old ones".
The following code takes this approach:
create unique index image_tags_unique on image_tags(image_id, tag_id);
insert into image_tags
select image_id, <newtagid>
from image_tags
where tag_id = <oldtagid>
on duplicate key ignore;
delete from image_tags
where tag_id = <oldtagid>;
The first step creates a unique index on image_tags, so duplicates are not allowed in the table.
The second inserts the new records, ignoring any errors generated by duplicates.
The third deletes the old records.
To be honest, you can also do this with the ignore keyword on update instead of insert step. However, ignore is very general, so -- in theory -- there could be another error being ignored incorrectly. The on duplicate key ignore is much more specific about what is allowed.
I think this will add new rows to images_tags that meet your criteria.
insert into images_tags (image_id, tag_id)
select image_id, tag_id
from (select i.image_id image_id, t.id tag_id
from images_tags i
join tags t
where i.tag_id = $oldtag and t.id != $oldtag) crossp
left join images_tags existing
using (image_id, tag_id)
where existing.id is null
group by image_id
The crossp subquery creates a full cross-product between all the image_ids that currently have the old tag, and all the tags other than the old tag. Then we do a left join with the existing images_tags, and use the null check to filter out all the pairs that already exist. This generates a list of image_id and tag_id pairs that do not match anything in the database. Finally we group by image_id so we just add one new row per image.
After you do this, you can delete the rows with tag_id = $oldtag.
The only problem with this is that it changes the IDs of the images_tags rows. There may be a way to do it all in one step with an UPDATE query, which wouldn't have that problem, but I'm not sure how to turn my query into that.