Remove all occurrences of ids from comma separated string in single MySQL query

Question

I have a table like this:

id	shop_id	collection_id	collection_title	product_ids	created_on	updated_on
20735	50	263607681176	Home page		2021-04-09T01:06:13Z	2021-04-13T23:26:14Z
20736	50	264253735064	Summer Collection	6623623413912,6623624822936,6623625085080,6623624691864,6623624659096,6623623741592,6623623610520,6623623577752	2021-04-09T01:06:13Z	2022-03-30T06:58:34Z
20737	50	264253736541	Winter Collection	6623624822936,6623625085080,6623624691864,6623624659096	2021-04-09T01:06:13Z	2022-03-30T06:58:34Z
20738	70	264253767832	Under $100	6623623413912,6623625085080,6623624822936,6623624790168,6623623905432,6623623872664,6623623839896,6623623774360	2021-04-09T01:06:13Z	2021-04-13T23:26:14Z

My input ids are:

6623625085080
6623624659096

I want a table like this after a query (passed ids should be removed from the table):

id	shop_id	collection_id	collection_title	product_ids	created_on	updated_on
20735	50	263607681176	Home page		2021-04-09T01:06:13Z	2021-04-13T23:26:14Z
20736	50	264253735064	Summer Collection	6623623413912,6623624822936,6623624691864,6623623741592,6623623610520,6623623577752	2021-04-09T01:06:13Z	2022-03-30T06:58:34Z
20737	50	264253736541	Winter Collection	6623624822936,6623624691864	2021-04-09T01:06:13Z	2022-03-30T06:58:34Z
20738	70	264253767832	Under $100	6623623413912,6623625085080,6623624822936,6623624790168,6623623905432,6623623872664,6623623839896,6623623774360	2021-04-09T01:06:13Z	2021-04-13T23:26:14Z

I have already used this query and it works for the single product_ids in there:

UPDATE collections SET product_ids = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', product_ids, ','), ',6623625085080,', ',')) WHERE FIND_IN_SET('6623625085080', product_ids) AND shop_id = 50;

But, I want to do that by passing multiple product_ids in a single Update query.

Here you can see this screenshot, that I have tested,

And here is the SQL Fiddle link also: http://sqlfiddle.com/#!9/bba9ff/1

Answer 1

What I would do is crafting a function that

• given a comma-separated original string of products and
• given a comma-separated string of to-be-removed products,

outputs the remaining products from the first string of products.

Once you get this function, you can use it into your UPDATE statement avoiding overcomplexity in it.

The function I was thinking is the following one:

DELIMITER $$

DROP FUNCTION IF EXISTS remove_product_ids;
CREATE FUNCTION remove_product_ids (
    all_products        VARCHAR(500), 
    products_to_remove  VARCHAR(500)
)
RETURNS 
    VARCHAR(500) 
DETERMINISTIC
BEGIN
    DECLARE num_products    INT        DEFAULT LENGTH(REGEXP_REPLACE(products_to_remove, '[^,]', ''))+1;
    DECLARE product         CHAR(13);

    WHILE num_products > 0 DO
        SET product = SUBSTRING_INDEX(SUBSTRING_INDEX(products_to_remove, ",", num_products), ',', -1);
        SET all_products = REGEXP_REPLACE(all_products, CONCAT(product, ',|,', product), '');
        SET num_products = num_products - 1;
    END WHILE;

    RETURN all_products;
END$$

DELIMITER ;

The while you can see here, loops over the number of products to be removed. The three operations that are carried out in there are:

1. extracting the product to remove from the products_to_remove string
2. removing the product from the all_products original string
3. updating the number of products to be removed

Once you get it working, you are able to call the UPDATE function like this:

SET @input_ids = '6623625085080,6623624659096';

UPDATE 
    collections 
SET 
    product_ids = remove_product_ids(product_ids, @input_ids);

Running the script into SQLFiddle will put two problems in place:

1. SQLFiddle is unable to process delimiters (Execute triggers stored procedures on SqlFiddle. Mysql), hence it requires us to change all ';' to '//' and select the different kind of delimiter from the options (though if you work with your mysql client, it should recognize 'DELIMITER')
2. If you run the update function along with the creation of the function, it fires a 'Thread stack overrun' exception, which can be fixed by specifying a higher number for the stack memory. This is something you can't do within SQLFiddle, but you would be able to alter that value for your mysql client (in case you need it), changing the variable 'thread_stack' in the 'my.cnf' file found in your mysql folder, like pointed in this stack overflow thread How do I resolve a mysql Thread Stack Overrun?.

Here's the SQLFiddle I've run: http://sqlfiddle.com/#!9/40cb18.