DROP FUNCTION IF EXISTS SPLIT_STR;

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

DELIMITER //

DROP PROCEDURE IF EXISTS auth_categs//

create procedure auth_categs(IN to_id INT(10), IN from_list VARCHAR(200))
begin
        DECLARE commercial_category_id BIGINT(20);
	  DECLARE from_list BIGINT(20);
        DECLARE cat_exists BIGINT(20);
        DECLARE user_elem_id BIGINT(20);
	  DECLARE commercial_category_lists VARCHAR(200);

        # get user_elem and a mysql set with commercial_categs ex: 17,45,32
        DECLARE list_ids CURSOR FOR
            SELECT ac.user_elem_id, GROUP_CONCAT(ac.commercial_category_id SEPARATOR ',') as commercial_category_lists
            FROM authored_categories as ac
            WHERE FIND_IN_SET(ac.commercial_category_id, from_list)
		Group by ac.user_elem_id;

	  #parse the results
        OPEN list_ids;
        lists_loop: LOOP
        FETCH list_ids INTO user_elem_id, commercial_category_lists;
		IF FIND_IN_SET(to_id, commercial_category_list)=0 THEN
		    # if not in set update the first one
			UPDATE authored_categories  SET  commercial_category_id=to_id WHERE commercial_category_id=SPLIT_STR(commercial_category_list, ',', 1) and user_elem_id=user_elem_id;
		END IF;
	  DELETE from authored_categories WHERE commercial_category_id in (from_list) and user_elem_id=user_elem_id;

        END LOOP lists_loop;
end
//