mysql procedure : Result consisted of more than one row with select statement

Question

I am creating one procedure for my regular repeated job.

Within this, there is one steps to insert multiple rows from one table into temporary table.

CREATE TABLE `tmpUserList` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_type` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
      `first_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
      `last_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    ..... some more queries.


        INSERT INTO  tmpUserList ( 
                SELECT  id, user_type,first_name,last_name,  from user  where  id in  (usersId) 
        );


    SELECT * FROM tmpUserList; // return the result

But it gives me error : Result consisted of more than one row

@P.Salmon nope.Its just inserting some rows in temp table and return the result from temp table by calling procedure — RIYAJ KHAN, Dec 25 '18 at 11:37

score 1 · Accepted Answer · answered Dec 25 '18 at 11:23

1

Correct INSERT SELECT syntax:

INSERT INTO tmpUserList(id, user_type,first_name,last_name)
SELECT id, user_type,first_name,last_name 
FROM user  
WHERE id IN (usersId);

If usersId contains multiple values you could use:

WHERE FIND_IN_SET(id, usersId);  -- table scan

Related: MySQL Prepared statements with a variable size variable list

answered Dec 25 '18 at 11:23

Lukasz Szozda

139,860
19
198
228

will it insert multiple rows from select result using insert into tmpUserList? – RIYAJ KHAN Dec 25 '18 at 11:32
@RIYAJKHAN That is exactly what `INSERT SELECT` does. It will insert as many rows as independent `SELECT` returns – Lukasz Szozda Dec 25 '18 at 11:33
it gives me error : Result consisted of more than one row – RIYAJ KHAN Dec 25 '18 at 11:36
@RIYAJKHAN Why are you not posting entire code? You probably have `SELECT ... INTO` statement inside your SP. – Lukasz Szozda Dec 25 '18 at 11:38
I have copies your answer as it is.no INTO statement – RIYAJ KHAN Dec 25 '18 at 11:39
1

my bad,there was correction in other statement. Upvoted and accepted @Lukasz – RIYAJ KHAN Dec 25 '18 at 12:14

mysql procedure : Result consisted of more than one row with select statement

1 Answers1