0

I'm trying to update 1 field from another table's data but I'm getting the error #1242 - Subquery returns more than 1 row.

The query I'm running is:

UPDATE oc_order AS o 
SET o.date_added = ( SELECT date_added FROM oc_order_history 
                    WHERE order_id = o.order_id 
                    AND order_status_id = 5)

Any ideas on where it's going wrong or how I can fix?

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
Syztemlord
  • 21
  • 1
  • 5
  • Run your subquery on its own for the `orderId` and you will see why. – Stu Oct 28 '21 at 09:26
  • 2
    Does this answer your question? [#1242 - Subquery returns more than 1 row - mysql](https://stackoverflow.com/questions/12597620/1242-subquery-returns-more-than-1-row-mysql) – Musabbir Mamun Oct 28 '21 at 09:27

1 Answers1

0

You should use JOIN.

UPDATE oc_order oc
INNER JOIN 
(
  SELECT date_added ,order_id
  FROM oc_order_history 
  WHERE order_status_id = 5
) as oh on oc.order_id=oh.order_id
set oc.date_added=oh.date_added ;

I supposed that order_id is the key for JOIN condition. Test it and let me know if it helps. It would be better if you gave some examples data to get a right answer.

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28