MySQL IN Clause in SQL query with User Defined Function

Question

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.

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

    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

Returns the function the string '188,190,189' or a resultset with 3 records? — Frank
– Frank, Commented Jun 28, 2016 at 9:19
@Frank function returns a string with comma separated values — Om Bissa
– Om Bissa, Commented Jun 28, 2016 at 9:20
Thats the problem. It must return a result set in this context — Frank
– Frank, Commented Jun 28, 2016 at 19:10

Blank · Accepted Answer · 2016-06-28 09:27:16Z

3

Try this;)

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

answered Jun 28, 2016 at 9:27

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL IN Clause in SQL query with User Defined Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related