Change order status in Magento via SQL

Question

I am having an issue my third party payment gateway have changed a cancelled order status of an order to pending so now I am stuck with a pending order for ever.

I was speaking over the phone with the client he said had a problem with the credit card so I cancelled her order and when the backend received the notice form bank then it changed to pending.

So is there anyway to cancel it again? perhaps via SQL?

Regards,

Answer 1

I'm running enterprise, and managed to do it with:

UPDATE sales_flat_order_grid SET status = 'processing';  
UPDATE sales_flat_order SET state = 'processing', status = 'processing';

Somebody had managed to wipe all the order statuses, and that's how I was easily able to get them back.

Of course, if you wanted to update specific records, you'd add WHERE entity_id = '12345' or whatever to the end of the query.

And always backup the database before you run any queries like the above!

Answer 2

Previous answers are missing the request updating the 'sales_flat_order_grid' table. If the question is still active the full request is:

UPDATE sales_flat_order_grid SET status = 'canceled' WHERE increment_id = <order_increment_id>;
UPDATE sales_flat_order SET state='canceled', status='canceled' WHERE increment_id =<order_increment_id> (or WHERE entity_id = <order_id>);