Change order status in Magento via SQL

Change order status in Magento via SQL

August 20, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Change order status in Magento via SQL

Resolving Stuck Orders: Changing Order Status via SQL in Magento

As a senior developer and technical blogger, I understand how frustrating it can be when payment gateways unexpectedly change order statuses, leaving you with persistent “pending” orders that need to be resolved manually. In this post, we’ll explore how to handle such situations by directly updating the order status using SQLite or MySQL queries in Magento.

Understanding the Issue

The problem lies in the asynchronous nature of third-party payment gateways. When an order is cancelled due to a failed transaction, the gateway may still send a “pending” status update later, which Magento interprets as a new state for the order. This results in a stuck “pending” order that requires manual intervention.

Solution: Updating Order Status via SQL

Step 1: Identify the Database and Table

In Magento, orders are stored in the sales_order table located within the database you’re using (e.g., SQLite or MySQL). To access this information:

  • For SQLite: Navigate to the root folder of your Magento installation.
  • For MySQL: Connect to your server using a MySQL client like phpMyAdmin or MySQL Workbench.

Step 2: Find the Order ID

To identify the specific order, you’ll need its entity_id (order ID). You can find this in the Magento admin panel by searching for the customer’s name or order details.

For example, if your customer is named “John Doe” and placed an order with a transaction ID of “1234567890”, follow these steps:

  • Log into your Magento admin panel.
  • Navigate to Sales > Orders.
  • Search by Customer Name: John Doe
  • Click on the specific order.
  • Note down the Order ID (usually displayed as “Order #” at the top of the page).

Step 3: Update the Order Status via SQL

Now that you have the order ID, you can update its status using an SQL query. Here’s how to proceed:

For SQLite:

Open a text editor and create a new file with your preferred text editor (e.g., Sublime Text or Visual Studio Code). Paste the following query into this file:

UPDATE sales_order SET status = 'cancelled' WHERE entity_id = <Your_Order_ID>;

Replace <Your_Order_ID> with the actual order ID you noted earlier.

For MySQL:

If you’re using MySQL, open your preferred MySQL client and run the following query:

UPDATE sales_order SET status = 'cancelled' WHERE entity_id = <Your_Order_ID>;

Again, replace <Your_Order_ID> with the actual order ID from step 2.

Best Practices

Use a Backup Before Modifying Data

Before executing any SQL queries on your live database, always create a backup using Magento’s built-in backup and restore functionality. This ensures you can revert changes if needed.

Test Changes in a Development Environment First

Whenever possible, replicate the issue in a development environment that mirrors your production setup. Validate the SQL query before applying it to your live store.

Conclusion

Changing order status via SQL is a powerful tool for resolving stuck orders when third-party payment gateways unexpectedly modify them. By following this guide, you can quickly and accurately update the order status to “cancelled” or any other desired state using SQLite or MySQL queries directly in Magento.

Remember, always proceed with caution when modifying database records manually. If you’re unsure about any aspect of the process, don’t hesitate to consult a professional developer who specializes in e-commerce platforms like Magento.

At Laravel Company, we offer expert Magento development services and can assist you with complex issues like this one. Don’t let stubborn “pending” orders hold your business back—take control of your order management today by applying the techniques covered in this post.

Database Admin Orders Customers

Our network