skip to Main Content

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

2

Answers


  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;
    
    Login or Signup to reply.
  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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search