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
You are setting a value so you don’t need the column alias …
remove the
AS fence_ids
and you missed also command terminater for each delete
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:Third, when you use:
it will treat
@fence_ids
as a single ID, not a list of IDs. So this is equivalent to:If you want to search for something in a comma-separated list, you need to use
FIND_IN_SET
: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.You don’t need to do this in two statements:
You can just do: