Insert - Subquery returns more then 1 row

[wysiwygGER01]

Hi,

I'm trying to get this SQL statement to work:

INSERT INTO lnkroletasks(taskid, roleid)VALUES('108', (SELECT id FROM tblroles WHERE role = 'Customer Service Manager' OR role='Engineering Manager'))

I think the error shows up because the first value to insert('108') is a static value. The second value to insert is the result of a select query.

I want to use '108' for all rows which are returned by the sub-select.
Is this something that can be done?

 

[ingresman]

Are you getting an error in which case what is it?
is taskid an integer in which casde get rid of the quotes
what happens if you run

SELECT id FROM tblroles WHERE role = 'Customer Service Manager' OR role='Engineering Manager'

do you get more than 1 row returned ?

 

[wysiwygGER01]

Thanks for your quick reply.

The error code is 1242 - Subquery returns more then 1 row.
Which is correct.

This code returns more then 1 row.

SELECT id FROM tblroles WHERE role = 'Customer Service Manager' OR role='Engineering Manager'

My questions is whether there is a way of telling MySQL to use the taskid 108 for all rows returned from the subquery?

Tried with ANY or ALL for the subquery as well but this didn't work either.

 

[r937]

INSERT 
  INTO lnkroletasks
     ( taskid
     , roleid )
SELECT 108
     , id 
  FROM tblroles 
 WHERE role = 'Customer Service Manager' 
    OR role = 'Engineering Manager'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[wysiwygGER01]

Perfect!
Thanks r937!