CODEWITHSUNDEEP
sqlmagento
jsjc
jsjc

Reputation: 1023

Change order status in Magento via SQL

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,

Upvotes: 8

Views: 24761

Answers (5)

Lorenzo Allievi
Lorenzo Allievi

Reputation: 1

please consider that changing the order state/status won't be enough: you should also cancel item(s) and amount information, joining the sales_flat_order_item.

Upvotes: 0

IssaBERTHE
IssaBERTHE

Reputation: 345

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>);

Upvotes: 9

Cuackmire
Cuackmire

Reputation: 29

Adding info to Silas answer: you may need to insert a new status in sale status history.

INSERT INTO sales_flat_order_status_history (`parent_id`,`is_customer_notified`,`is_visible_on_front`,`comment`,`status`,`entity_name`,`created_at`) VALUES (YOUR_ORDER_ID,0,0,'',NEW_STATUS_CODE,'order','2014-05-20 11:42:29')

Upvotes: 1

Silas Palmer
Silas Palmer

Reputation: 186

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!

Upvotes: 17

Jevgeni Smirnov
Jevgeni Smirnov

Reputation: 3797

What about SQL:

UPDATE sales_flat_order SET state="canceled", status="canceled" WHERE entity_id = {order id should be here} AND increment_id = {order increment id should be here}

In this query you should specify either entity_id or increment_id or both. As you wish.

WARNING

Make a backup of your database before you do change something. I didn't had any time to analyze the db structure and constraints related to an order.

What about PHP:

Run following PHP script from root folder:

error_reporting(E_ALL);
ini_set('display_errors', 1);
require_once('app/Mage.php');
Mage::app('admin');
$order = Mage::getModel('sales/order')->loadByIncrementId($id);//id is you order increment id 
OR
$order = Mage::getModel('sales/order')->load($id);//id is you order id 
if ($order->getId()) {
    $order->cancel()->save();
}

Upvotes: 0

Related Questions