如何将所有这些MySQL查询合并为一个？

Question

I have a lot of articles tagged with the tag "dog". I have a new tag now called "cat". I want to add the tag "cat" to all articles that already have the tag "dog". A few articles have both tags. I don't want to retag these with "cats".

In other words, if an article has the tag "dog" and doesn't yet have the tag "cat" I want to add the tag "cat" to it. Here is the script I wrote:

<?php 

    # Get "cat" tag id
    $sql = "SELECT `id`
            FROM tags
            WHERE name = 'cat'
            LIMIT 1";

    $cat_tag = mysql_fetch_assoc(mysql_query($sql));

    # Get "dog" tag id
    $sql = "SELECT `id`
            FROM tags
            WHERE name = 'dog'
            LIMIT 1";

    $dog_tag = mysql_fetch_assoc(mysql_query($sql));

    ######################################

    # Get all nodes tagged with "dog"   
    $sql = "SELECT `node_id`
            FROM node_tags
            WHERE `tag_id` = '" . $dog['id'] . "'";

    $query = mysql_query($sql);
    while($row = mysql_fetch_assoc($query)) {

        # Check to see if current node has "cat" tag already    
        $sql = "SELECT COUNT(*)
                FROM node_tags
                WHERE `node_id` = '" . $row['node_id'] . "'         
                AND `tag_id` = '" . $cat['id'] . "'";


        $check_already_exists = mysql_fetch_assoc(mysql_query($sql));

        # If node doesn't already have "cat" tag, then add it
        if($check_already_exists == '0') {
            $sql = "INSERT INTO `node_tags`(node_id, tag_id) 
                    VALUES('" . $row['node_id'] . "', '" . $cat['id'] . "')";
            mysql_query($sql);
        }
    }

?>

I want to be able to run this directly from my MySQL manager tool. So it can't have any PHP but should be one large SQL query. How to do this?

Answer 1

The following query gets all "dogs" that have no cat id. It then inserts them into the table:

insert into node_tags(node_id, tag_id)
    SELECT id, $cat['id']
    FROM tags t join
         node_tags nt
         on t.node_id = nt.node_id
    WHERE t.name = 'dog'
    group by id
    having max(case when tag_id = $cat['id'] then 1 else 0 end) = 0