I'm getting a weird error in one of my orders where the header status is processing but order details are all cancel. Can anyone give me a SQL command that I can use to update the table status for the orders tables?

enter image description here

enter image description here

3 Answers 3


You can use this one:

UPDATE sales_order SET status = 'your status' WHERE increment_id = '000000670' ;  
  • Thank you very much for your input. My problem was way beyond what I thought as it was created by the payment gateway plugin which made a mess of the status in various tables. Anyway all is fine now. Thanks for you help.
    – Alex Lim
    Commented Feb 1, 2019 at 10:22
  • you're welcome!
    – fmsthird
    Commented Feb 1, 2019 at 10:49

You should update the tables sales_order and sales_order_grid as Amit Bera said. In sales_order you need to update the state column too in order to have consistent data. So the correct SQL statement for updating the sales_order table is (assuming you want to cancel the order):

UPDATE sales_order SET status = 'canceled', state = 'canceled' WHERE increment_id = '{ORDER_INCREMENT_ID}';

You should check before if the related data in sales_order_item table is consitent (i.e. qty_canceled is set)


There are main two tables where Magento store order status.

  • sales_order
  • sales_order_grid

So, you have to two queries for an orders

UPDATE sales_order SET status = {ORDER_STATUS_CODE} WHERE increment_id = '{ORDER_INCREMENT_ID} ';
UPDATE sales_order_grid SET status = {ORDER_STATUS_CODE} WHERE increment_id = '{ORDER_INCREMENT_ID}' ;

{ORDER_INCREMENT_ID} means 000001,00000670.

  • Thank you. I'm making progress buy now I'm left with the order items showing it's canceled. :(
    – Alex Lim
    Commented Jan 31, 2019 at 13:38
  • Alex Lim, this query is not proper for all case, Magento different logic on order process. Cancel and complete and close is not support for this query,
    – Amit Bera
    Commented Jan 31, 2019 at 14:18
  • Thanks Amit. I jumped into CPanel and used phpMyAdmin to dissect the structure myself. It looks like there is more than meets the eye. In sales_order table, there are 2 columns which are state and status as well as sales_order_grid table which has a status plus sales_order_items which has qty_canceled too. Managed to edit the records and get everything fine now. Your post is of great help to get me started.
    – Alex Lim
    Commented Feb 1, 2019 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.