mysql n:m relationship delete query 1242 Subquery returns more than 1 row

Question

I have two SQL Tables, 'products' and 'tags'. They have an n:m relationship, using a third table 'product_tags'.

I want to delete some products and corresponding tags all together.

Let's say, I have products.product_id=3, that product have tags.tag_id=3, tags.tag_id=5

product_tags table

product_id 3 tag_id 3
product_id 3 tag_id 5

delete from tags where tag_id in (select product_tags.tag_id from product_tags where product_id =3);
delete from tags where tag_in = any (select product_tags.tag_id from product_tags where product_id=3);

either will produce

0 row(s) affected, 1 warning(s): 1242 Subquery returns more than 1 row

So, how can I accomplish this?

Answer 1

First of all you most likely want to delete tags that are not used with other products. E.g. if a tag with tag_id = 3 is also used with some other product let's say product_id = 1 then you shouldn't delete this tag.

Secondly if you have proper parent-child relationships enforced with foreign keys you have to delete rows from tables in proper sequence. First you should delete rows from product_tags.

That being said your code to safely delete a product along with unused tags might look

DELIMITER //
CREATE PROCEDURE delete_product(IN _product_id INT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_tags_to_be_deleted;

  START TRANSACTION;

  CREATE TEMPORARY TABLE tmp_tags_to_be_deleted(tag_id INT PRIMARY KEY);
  INSERT INTO tmp_tags_to_be_deleted
  SELECT tag_id
    FROM product_tags t
   WHERE product_id = _product_id
     AND NOT EXISTS
  (
    SELECT *
      FROM product_tags
     WHERE tag_id = t.tag_id
       AND product_id <> t.product_id
  );

  DELETE 
    FROM product_tags
   WHERE product_id = _product_id;

  DELETE t
    FROM tags t JOIN tmp_tags_to_be_deleted x
      ON t.tag_id = x.tag_id;

  DELETE 
    FROM products
   WHERE product_id = _product_id;

  COMMIT;
END//
DELIMITER ;

Usage:

CALL delete_product(3);

Here is SQLFiddle demo