problem in nested query Mysql question

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

returns multiple values.

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

1 03 N
2 03 N
3 03 N

thats not possible with that syntax. you have to loop throud all results from SELECT id FROM billing_period order by billing_start_date DESC LIMIT 0,1 — Fender
– Fender, Commented Feb 4, 2011 at 10:33

rxmnnxfpvg · Accepted Answer · 2011-02-04 10:39:18Z

1

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

answered Feb 4, 2011 at 10:39

rxmnnxfpvg

31.2k50 gold badges129 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anothershrubery · Accepted Answer · 2011-02-04 10:37:36Z

0

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

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

answered Feb 4, 2011 at 10:37

anothershrubery

21.1k16 gold badges56 silver badges102 bronze badges

Collectives™ on Stack Overflow

problem in nested query Mysql question

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related