MySQL IN Clause in SQL query with User Defined Function

I am using following query to update all the children of particular topic.

我正在使用以下查询来更新特定主题的所有孩子。

UPDATE topics SET reuse = 0 WHERE topic_id IN (SELECT GetChildTopics(187));

Where

SELECT GetChildTopics(187);

returns "188,190,189" but my update query is updating only first row with topic_id = 188, instead of updating first topic only, it should update all 3 topics.

返回“188,190,189”,但我的更新查询仅更新第一行topic_id = 188,而不是仅更新第一个主题,它应更新所有3个主题。

When I put the values manually it works fine.

当我手动输入值时,它工作正常。

UPDATE topics SET reuse = 0 WHERE topic_id IN (188,190,189);

Can anyone suggest what's wrong I am doing here?

谁能说明我在这里做错了什么?

Here is the code for GetChildTopics MySQL Function

这是GetChildTopics MySQL函数的代码

    CREATE DEFINER=`root`@`localhost` FUNCTION `GetAncestry`(GivenID INT) RETURNS varchar(1024) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_topic_id,-1) INTO ch FROM
        (SELECT parent_topic_id FROM topic_list WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END

1 个解决方案

#1

Try this;)

UPDATE topics SET reuse = 0 WHERE FIND_IN_SET(topic_id, GetChildTopics(187));