CODEWITHSUNDEEP
mysqlsqlstored-procedures
learner
learner

Reputation: 2789

How to implode a query result inside a mysql5 stored procedure?

I want to implode a query result using "," inside my mysql stored procedure. How can I do that?

For example,

CREATE DEFINER=`root`@`%` PROCEDURE mysp()

BEGIN

    DROP TABLE IF EXISTS  temp;
    CREATE TABLE temp (ids INT(11) , typeid INT(11));

   INSERT INTO temp SELECT id,typeid FROM table_mine1 ..etc

   INSERT INTO temp SELECT id,typeid FROM table_mine2 ..etc

After this I want to fetch all "ids" from temp table and make it as comma separated.

Because I need to write one another query like this

DELETE FROM main_table WHERE ids NOT IN (comma separated ids of temp table.)

How to implode ids of my temp table using comma?

Upvotes: 2

Views: 5796

Answers (2)

jbrond
jbrond

Reputation: 727

Have a look at the GROUP_CONCAT function? something like:

SELECT GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') FROM temp;

Upvotes: 11

Devart
Devart

Reputation: 121932

I think you could do this -

DELETE mt FROM
  main_table mt
LEFT JOIN temp t
  ON mt.id = t.id
WHERE
  t.id IS NULL;

This query will delete all records in main_table which does not exist in temp table. There is no need in comma separated ids.

Upvotes: 1

Related Questions