Search code examples
mysqlsqlopencart

Having problem with sql query in Opencart database

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?

Solution

  • 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.