Insert to one table, based on another

[Jafo]

Well, here is another mysql question from me today and I have searched to no real avail.

Have one table (table2) with user options. Example:

userid ¦ option_name ¦ option_value
1  notifyme  1
2  lastname  smith
3  notifyme  0

After a certain action in the code, I need to take all the user ids and place them in another table if an only if "notifyme" is 1 (true).

Basically, this is what I tried:

insert into table1 
set user_id = (select userid from table2 
where option_value = 1 
and option_name = 'notifyme'), item_id = 4

I keep getting this error: "subquery returns more than one row"

What am I doing wrong?

[coopster]

It's hard to tell from your example what columns are in table1, but I'm going to guess here ...

INSERT INTO table1 
 (user_id, item_id) 
 SELECT table2.userid, 4 
  FROM table2 
  WHERE table2.option_value = 1 AND table2.option_name = 'notifyme' 
;

[syber]

The problem is that your subquery is returning more than one row. Since user_id can only hold one value, the operation results in an error. To solve the problem use the DISTINCT keyword.

insert into table1
set user_id = (select DISTINCT userid from table2
where option_value = 1
and option_name = 'notifyme'), item_id = 4

[IndiaMaster]

Your query is correct, as far as I see. But I think there is more than one row in table1 which meets the codition specified in the subquery.

[phranque]

try running the subquery standalone as

select * from table2
where option_value = 1
and option_name = 'notifyme'

and see if that provides any clues...