problem in nested query Mysql question

below is my query and I am getting a "Subquery returns more than 1 row" error:

INSERT INTO billing_temp (`billing_period_id`,`soc_id`,`bill_y_n`) 
  VALUES(
      (SELECT `id` FROM `billing_period` ORDER BY `billing_start_date` DESC LIMIT 0,1),
      (SELECT `id` FROM `milk_producer` WHERE active='1'),
      'N'
  )

SELECT `id` FROM `billing_period` ORDER BY `billing_start_date` DESC LIMIT 0,1

re开发者_Go百科turns multiple values.

I want that these multiple values will insert into table, e.g:

1 03 N
2 03 N
3 03 N  

The problem is you can't perform an INSERT INTO ... SELECT and and INSERT INTO ... VALUES. Choose one.

In your case, there is an illogical correlation upon inserting data by row -- unless milk_producer only has 1 row WHERE active='1'. Similarly, if you're trying to load multiple rows, why are you LIMITing the inner query to 1 result?

INSERT INTO billing_temp (`billing_period_id`,`soc_id`,`bill_y_n`)
    SELECT `id`, ??, 'N' FROM `billing_period` 
    ORDER BY `billing_start_date` DESC

You need to loop through the SELECT statement. Have a look at cursors.

http://dev.mysql.com/doc/refman/5.0/en/cursors.html