How do I use a select Subquery in MYSQL

Question

I have two databases that I'm trying to transfer data between.

I'm working on getting orders from one to the other right now but I've run into something that I can't figure out the correct way to do it.

Here's the query thus far...

INSERT INTO `NEWTEST`.`Order_LineDetails`

( OrderLineItem_ID, Customer_ID, Order_ID )

SELECT

OrderDetailID,

(    
SELECT o.CustomerID
FROM `OLDTEST`.`Order_Details` od
JOIN `OLDTEST`.`Orders` o ON  o.OrderID = od.OrderID
),

OrderID

FROM `OLDTEST`.`Order_Details`

This is returning an error of '#1242 - Subquery returns more than 1 row'.

The result I'm going for would be to get...

OLDTEST.Order_Details           -> NEWTEST.Order_LineDetails
OLDTEST.Order.CustomerID        -> NEWTEST.Customer_ID
OLDTEST.Order_Details.OrderID   -> NEWTEST.Order_ID

What am I missing?

:::::: EDIT :::::::

This is now correct and works fine.

SELECT

  od.OrderDetailID,
  o.CustomerID,
  od.OrderID

FROM `OrderProcessing`.`Order_Details` od

JOIN `OrderProcessing`.`Orders` o ON o.OrderID = od.OrderID

LIMIT 100

bfavaretto · Accepted Answer · 2012-07-31 22:37:56Z

5

When you use a subquery in the SELECT clause as you did, it must return a single row, hence the error you're getting.

Actually you don't seem to need the subquery here:

INSERT INTO `NEWTEST`.`Order_LineDetails`
SELECT
    od.OrderDetailID,
    o.CustomerID,
    od.OrderID
FROM `OLDTEST`.`Order_Details` od
JOIN `OLDTEST`.`Orders` o ON  o.OrderID = od.OrderID

(Before running that, comment out the INSERT line to make sure it's giving the results you expect).

edited Jul 31, 2012 at 22:37

answered Jul 31, 2012 at 20:19

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

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

3 Comments

Monty Over a year ago

Thanks for this. It saved a bit of headache.

bfavaretto Over a year ago

Could we see some sample data, maybe on sqlfiddle.com? That select query should give you multiple rows, unless there are no matching order details for each orders. If so, you could use a LEFT OUTER JOIN instead of your current (implied INNER) JOIN, but that should just give you a bunch of NULL values, not very useful...

Monty Over a year ago

I took a third look at it and you are correct. Sorry for the mishap I've corrected the above.

frostmatthew · Accepted Answer · 2012-07-31 20:17:45Z

0

MySQL subquerys can only contain one result (and apparently yours is returning more). You can add a limit to fix this:

SELECT o.CustomerID
FROM `OLDTEST`.`Order_Details` od
JOIN `OLDTEST`.`Orders` o ON  o.OrderID = od.OrderID LIMIT 1

answered Jul 31, 2012 at 20:17

frostmatthew

3,2964 gold badges44 silver badges53 bronze badges

1 Comment

frostmatthew Over a year ago

In that case are you sure that subquery is doing what you want? You'll need to adjust the subquery to return the results you are expecting while returning only one row.

Collectives™ on Stack Overflow

How do I use a select Subquery in MYSQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related