Sub query returns more than 1 row

Question

Below is my mysql Query:

CREATE TEMPORARY TABLE temp_user_Details (
    user_id INT(10) NOT NULL,
    user_name NVARCHAR(100) NOT NULL,
    user_email NVARCHAR(100) NOT NULL
);


INSERT INTO temp_user_Details (user_id, user_name, user_email)
    (SELECT user_id, CONCAT(first_name," ",last_name) AS UserName, email
        FROM users WHERE reportingManager = "abcd");

I am getting the following error:

Error Code: 1242.

Subquery returns more than 1 row.

I am no able to understand what exaclty is the error as in users table user_id column is primary key and auto incremental.

just take out the `(` and `)` you don't need it on an insert statement and mysql can't deal with it. — Hogan, Nov 24 '15 at 05:19
Also check this: http://stackoverflow.com/questions/5253302/insert-into-select-for-all-mysql-columns. You can insert many values and columns at once. — Kostas Mitsarakis, Nov 24 '15 at 05:26
@KostasMitsarakis I am using the same syntax as mentioned in Stack you shared. But m facing problem. — Rahul Munjal, Nov 24 '15 at 06:27

score 0 · Answer 1 · answered Nov 24 '15 at 04:27

0

That's strange. My guess is that it is caused by the parentheses? Have you tried removing them, like this? Also, the alias has no use being there.

INSERT INTO temp_user_Details (user_id,user_name,user_email)
 SELECT user_id,CONCAT(first_name,' ',last_name),email
FROM users WHERE reportingManager = 'abcd';

answered Nov 24 '15 at 04:27

Ruslan

2,691
1
19
29

Ya i have tried this also, no luck. I also tried renaming the column names in temporary table. But the result is same. – Rahul Munjal Nov 24 '15 at 04:29
@RahulMunjal This is probably a shot in the dark, but are you sure that those two queries are the *only* thing you are running, and the error is not in another query that comes after your two? – Ruslan Nov 24 '15 at 04:30
I am sure @Ruslan. only these two queries are present in my stored procedure. Nothing else is present. – Rahul Munjal Nov 24 '15 at 04:31
@RahulMunjal `show variables where variable_name in ('innodb_version','version_comment')` says? – Drew Nov 24 '15 at 04:45
1

`innodb_version : 5.6.26`. `version_comment: google` – Rahul Munjal Nov 24 '15 at 04:56
It should work. Try: WHERE reportingManager IN 'abcd' (just in case'). – Kostas Mitsarakis Nov 24 '15 at 14:08

Sub query returns more than 1 row

1 Answers1