Created
December 10, 2025 17:55
-
-
Save florinel-chis/2c3a33df46a6b0da6e89c2ad74a8ca2f to your computer and use it in GitHub Desktop.
SQL to revert the status from cancelled to pending_payment
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- ⚠️ Do this on a backup / non-prod first | |
| START TRANSACTION; | |
| -- 0) Set your order increment ID | |
| SET @increment_id := '100000123'; -- <-- change this | |
| -- 1) Inspect current order header | |
| SELECT entity_id, increment_id, state, status, | |
| base_total_canceled, total_canceled | |
| FROM sales_order | |
| WHERE increment_id = @increment_id; | |
| -- 2) Get the order_id into a variable | |
| SELECT @order_id := entity_id | |
| FROM sales_order | |
| WHERE increment_id = @increment_id; | |
| -- Safety check: make sure we actually found something | |
| SELECT @order_id AS order_id; | |
| -- 3) Inspect items before touching them | |
| SELECT item_id, sku, qty_ordered, qty_canceled, qty_invoiced, qty_refunded, qty_shipped | |
| FROM sales_order_item | |
| WHERE order_id = @order_id; | |
| -- 4) Change order state/status back to pending_payment | |
| UPDATE sales_order | |
| SET state = 'pending_payment', | |
| status = 'pending_payment' | |
| WHERE entity_id = @order_id; | |
| -- (Optional but recommended) Clear canceled totals on the order, | |
| -- so reports don’t still see it as canceled | |
| UPDATE sales_order | |
| SET base_total_canceled = 0, | |
| total_canceled = 0, | |
| base_discount_canceled = 0, | |
| discount_canceled = 0, | |
| base_shipping_canceled = 0, | |
| shipping_canceled = 0, | |
| base_tax_canceled = 0, | |
| tax_canceled = 0 | |
| WHERE entity_id = @order_id; | |
| -- 5) Sync the sales_order_grid status | |
| UPDATE sales_order_grid | |
| SET status = 'pending_payment' | |
| WHERE entity_id = @order_id; | |
| -- 6) Reset qty_canceled on all items for this order | |
| -- (full uncancel – if you need partial, narrow the WHERE clause) | |
| UPDATE sales_order_item | |
| SET qty_canceled = 0 | |
| WHERE order_id = @order_id; | |
| -- 7) (MSI only) Inspect reservations related to this order | |
| -- Decide manually if you need to re-reserve stock | |
| SELECT * | |
| FROM inventory_reservation | |
| WHERE metadata LIKE CONCAT('%', @increment_id, '%'); | |
| -- 8) Final sanity checks | |
| SELECT entity_id, increment_id, state, status, | |
| base_total_canceled, total_canceled | |
| FROM sales_order | |
| WHERE entity_id = @order_id; | |
| SELECT item_id, sku, qty_ordered, qty_canceled, qty_invoiced, qty_refunded, qty_shipped | |
| FROM sales_order_item | |
| WHERE order_id = @order_id; | |
| -- If everything looks good: | |
| COMMIT; | |
| -- If something is wrong: | |
| -- ROLLBACK; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment