Insert With A Nested Select

[samtwilliams]

I need to selected the subscriber_Id from another tabled based on an email address, can't seem to work out why this nested select won't work.

 

Can someone help

 

Thanks

Sam

 

INSERT INTO  `pommo_subscriber_data` (  `data_id` ,  `field_id` ,  `subscriber_id` ,  `value` ) VALUES (
NULL ,  '1', (SELECT  `pommo_subscribers`.`subscriber_id` FROM  `pommo_subscribers` WHERE  `pommo_subscribers`.`email` =  'samtwilliams23'ORDER BY  `pommo_subscribers`.`subscriber_id` DESC),  'SamTEST');

Edited October 28, 2012 by samtwilliams

[Psycho]

That is not how you do an INSERT with a select. Take a look at the manual for that: http://dev.mysql.com...ert-select.html

 

I have no idea why you are inserting a NULL value for the first record - why not just leave that field out of the insert? Also, what is the purpose of putting an ORDER BY on the select that us being used to insert all the records?

 

This might be closer to what you want

INSERT INTO `pommo_subscriber_data`
(`data_id`, `field_id`, `subscriber_id`, `value`)

SELECT NULL, 1, `pommo_subscribers`.`subscriber_id`, 'SamTEST'
FROM `pommo_subscribers`
WHERE `pommo_subscribers`.`email` = 'samtwilliams23'

Edited October 28, 2012 by Psycho

[fenway]

Probably because it's an auto-increment value -- but still a bad, bad idea.

[Muddy_Funster]

I'm with Psycho - why in the name of everything that is sinful (it's a way bigger list than all that is holy) would you ever activly insert a null value into a table? if null is allowed, it should be a default value and nothing should be inserted into that field in the event that nothing is indeed being inserted into that field. during an update, fair enough, but for an insert?