MySql Stored procedure.... error in group_concat

Question

I have following query that works in phpMyAdmin

SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS fence_ids 
FROM asset_fence af 
INNER JOIN assets a ON a.vehicle_id = af.vehicle_id 
WHERE a.client_id=1

But the same query gives me error in stored procedure

ERROR IS:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@fence_ids = SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS ' at line 8

STORED PROCEDURE IS

DELIMITER $$

CREATE PROCEDURE `prcDeleteClient`(IN `f_client_id` INT, OUT AROWS INT)
BEGIN


    START TRANSACTION;


        @fence_ids = SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS fence_ids FROM asset_fence af INNER JOIN assets a ON a.vehicle_id = af.vehicle_id WHERE a.client_id=f_client_id


        DELETE
            asset_fence,
            geo_fence

        FROM 
            geo_fence gf INNER JOIN asset_fence af ON gf.fence_id = af.fence_id

        WHERE
            af.fence_id IN (@fence_ids)



        DELETE
            client,
            assets,
            asset_movement

        FROM 
            asset_movement am INNER JOIN assets a ON am.vehicle_id = a.vehicle_id
            assets a INNER JOIN client c ON a.client_id = c.client_id

        WHERE
            c.client_id=f_client_id


        SET @AROWS = ROW_COUNT();
        SELECT @AROWS as AROWS;

    COMMIT;
END $$

DELIMITER ;

Updated procedure is

DELIMITER $$

CREATE PROCEDURE `prcDeleteClient`(IN `f_client_id` INT, OUT AROWS INT)
BEGIN


    START TRANSACTION;


        DELETE
            af,
            gf
        FROM 
            geo_fence gf

            INNER JOIN asset_fence af ON gf.fence_id = af.fence_id
            INNER JOIN assets a ON a.vehicle_id = af.vehicle_id

        WHERE
            a.client_id=f_client_id;



        DELETE
            c,
            a,
            am

        FROM 
            asset_movement am INNER JOIN assets a ON am.vehicle_id = a.vehicle_id
            assets a INNER JOIN clients c ON a.client_id = c.client_id

        WHERE
            c.client_id=f_client_id;


        SELECT ROW_COUNT() AS AROWS;

    COMMIT;
END $$

DELIMITER ;

Error is: Unknown table 'c' in MULTI DELETE

What is the mistake and how can I rectify it?

Best Regards

Answer 1

You are setting a value so you don't need the column alias ... remove theAS fence_ids

    SELECT CONCAT("'", GROUP_CONCAT( af.fence_id SEPARATOR "','" ),"'") 
    FROM asset_fence af 
    INNER JOIN assets a ON a.vehicle_id = af.vehicle_id
    WHERE a.client_id=1

and you missed also command terminater for each delete

   DELETE
        asset_fence,
        geo_fence

    FROM 
        geo_fence gf INNER JOIN asset_fence af ON gf.fence_id = af.fence_id

    WHERE
        af.fence_id IN (@fence_ids);



    DELETE
        client,
        assets,
        asset_movement

    FROM 
        asset_movement am INNER JOIN assets a ON am.vehicle_id = a.vehicle_id
        assets a INNER JOIN client c ON a.client_id = c.client_id

    WHERE
        c.client_id=f_client_id;

Answer 2

First, your syntax for assigning variables is wrong, it needs the SET command before the variable name.

Second, if you want to use the result of a SELECT query as a value, you have to put parentheses around it:

SET @fence_ids = (SELECT ...);

Third, when you use:

WHERE af.fence_id IN (@fence_ids)

it will treat @fence_ids as a single ID, not a list of IDs. So this is equivalent to:

WHERE af.fence_id = @fence_ids

If you want to search for something in a comma-separated list, you need to use FIND_IN_SET:

WHERE FIND_IN_SET(af.fence_id, @fence_ids)

You also shouldn't add quotes around the values in your GROUP_CONCAT().

But you shouldn't use GROUP_CONCAT for this in the first place, you should just join with the query that returns all the IDs you want.

   DELETE
        af,
        gf
    FROM 
        geo_fence gf 
    INNER JOIN asset_fence af ON gf.fence_id = af.fence_id
    INNER JOIN assets a ON a.vehicle_id = af.vehicle_id 
    WHERE a.client_id=f_client_id;

You don't need to do this in two statements:

SET @AROWS = ROW_COUNT();
SELECT @AROWS as AROWS;

You can just do:

SELECT ROW_COUNT() AS AROWS;