Search code examples
insert-intomysql-error-1242

Insert into w/ multiple selects giving ERROR 1242: Subquery returns more than 1 row

I have table foo1 with columns UserID,TimeStamp; foo2 with columns userID,Level & table foo3 with columns userID,Timestamp.

I want to INSERT into foo1 all rows from foo3 where the UserID exists in table foo2.

I am getting ERROR 1242: Subquery returns more than 1 row with the following 

INSERT into foo1 (UserID,TimeStamp)
SELECT  
(SELECT UserID from foo2 as UserID),

(SELECT foo3.TimeStamp
from foo3
inner join foo2
ON foo3.UserID=foo2.UserID) as TimeStamp
Solution

  • If you want to INSERT into foo1 all rows from foo3 where the UserID exists in table foo2 then you should go through this:

    INSERT into foo1 (UserID,TimeStamp)
SELECT foo3.UserID,foo3.TimeStamp
from foo3
inner join foo2
ON foo3.UserID=foo2.UserID