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


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 Feb 1 at 10:22
  • you're welcome! – fmsthird Feb 1 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 Jan 31 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 Jan 31 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 Feb 1 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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