How to Use MySQL to Mark Orders Complete in WooCommerce

You can use MySQL to mark orders complete in WooCommerce by running an UPDATE query against the wp_posts table for classic order storage, or the wp_wc_orders table if your store runs High-Performance Order Storage. Which one applies to you depends on a setting most store owners never check. That’s step one below.

This guide walks through both versions of the query, step by step, plus the backup you need before running either one. If you manage a store with more than a handful of orders stuck in “processing,” this will save you a lot of clicking.

Why is MySQL used to Mark Orders Complete in WooCommerce?

WooCommerce stores every order as data in your site’s MySQL database, whether that’s the legacy post tables or the newer HPOS custom order tables.

When an order gets marked complete, that status change is a database update, not something that lives in a plugin’s memory or a temporary cache. This matters because MySQL handles it as a proper transaction. The update either goes through cleanly or it doesn’t, so you don’t end up with an order that’s half-updated or a status that gets lost if something times out mid-request.

It’s also why order history, refunds, and reporting all stay consistent. Everything reads from the same source of truth in the database, instead of different parts of your store showing different information about the same order.

What You’ll Need Before Starting

• Access to phpMyAdmin, Adminer, or SSH access to your server
• A recent full database backup, or the ability to create one right now
• Admin access to your WordPress dashboard
• Roughly ten minutes, most of which goes to the backup

No coding background is required for the direct SQL approach. If you plan to use the WP-CLI or PHP methods in Step 8, basic command-line comfort helps but isn’t strictly necessary since the commands are copy-paste ready. If you’re still setting up your store’s database and want the background on why WooCommerce needs MySQL in the first place, this breakdown of WooCommerce’s database requirements covers it.

Step 1: Check Which Order Storage System Your Store Uses

Before you write a single line of SQL, find out how your store stores orders right now. Guess wrong, and your query will run without any error and update exactly zero rows.

Since WooCommerce 8.2, released in October 2023, High-Performance Order Storage (HPOS) has been the default for new stores. Older stores may still run the legacy setup, where orders live as a custom post type inside wp_posts.

Here’s how to check:

1. In your WordPress admin, go to WooCommerce > Settings> Advanced > Features.
   
2. Look at the Order data storage section. If it’s High-Performance Order Storage (HPOS), your orders live in wp_wc_orders. or WordPress posts storage (legacy), then your orders live in wp_posts.
   

Not sure which screen you’re looking at? A quick database check settles it. Run this in phpMyAdmin:

SHOW TABLES LIKE 'wp_wc_orders';

If that returns a row, HPOS is active. If it returns nothing, you’re on the legacy system.

Most stores built or migrated after late 2023 run HPOS. Stores that have been live for years and have never touched this setting are usually still on legacy storage.

Understanding WooCommerce Order Statuses First

Every query in this guide hinges on one detail: the exact status value stored in the database. WooCommerce doesn’t store “Processing” or “Completed” as plain words. It stores them with a wc- prefix, and mixing that up is the single most common reason a status update query runs clean and changes nothing. WooCommerce’s official order statuses documentation covers the full lifecycle if you want the complete picture beyond the table below.

Dashboard Label	Database Value	Meaning
Pending payment	wc-pending	Order received, payment not yet confirmed
Processing	wc-processing	Payment received, order being prepared
On hold	wc-on-hold	Awaiting manual action, often for offline payments
Completed	wc-completed	Order fulfilled and finished
Cancelled	wc-cancelled	Order cancelled by admin or customer
Refunded	wc-refunded	Order fully or partially refunded
Failed	wc-failed	Payment attempt failed

Every query below targets wc-completed as the destination status and, in most cases, wc-processing as the source. Swap those values if your situation calls for a different starting point, like clearing out old on-hold orders instead.

Step 2: Back Up Your Database Before You Touch It

Skip this step, and you’re taking a risk with live order data. Don’t. For the full walkthrough with screenshots, see this WooCommerce database backup guide. The short version:

1. Log in to your hosting control panel or use a plugin like UpdraftPlus.
   
2. Create a full database backup, not just a partial export.
   
3. Download a copy to your own machine, not just the server.
   

If your host supports staging environments, test the query there first. This one habit catches more mistakes than any amount of careful typing.

Most hosts finish a full backup in under five minutes for a typical WooCommerce database. There’s no good reason to skip it.

Step 3: Access Your Database

You have two common ways in: phpMyAdmin through your host, or the command line if you’re comfortable with SSH.

Using phpMyAdmin:

1. Log in to your hosting Panel.
   
2. Open phpMyAdmin (cPanel, Plesk, and most managed WordPress hosts include it).
   
3. Select your WordPress database from the left sidebar.
   
4. Click the SQL tab at the top.
   

Using WP-CLI (if you have SSH access):

wp db cli

This drops you straight into a MySQL prompt scoped to your WordPress database. Either path gets you to the same place. Pick whichever one your hosting setup already gives you.

One detail that trips people up: not every WooCommerce install uses the wp_ table prefix. Some hosts and security plugins randomise it during setup as a security measure, so you might be looking at wp7x2_posts or something similar instead. Check your wp-config.php file for the $table_prefix value before running any query below, and swap the prefix in every example to match. If you’re curious why sites do this, this guide on changing the WordPress database prefix explains the reasoning.

Step 4: Run the Query for Legacy Order Storage (wp_posts)

If Step 1 confirmed your store is on legacy storage, this is your query. It updates every order currently marked “processing” to “completed”:

UPDATE wp_posts
SET post_status = 'wc-completed'
WHERE post_type = 'shop_order'
AND post_status = 'wc-processing';

Every WooCommerce order status carries the wc- prefix in the database. That’s easy to forget, and the most common reason this query silently does nothing.

Run it in phpMyAdmin’s SQL tab, then click Go. In the command line, paste it and press Enter. On a database with a few thousand orders, this finishes in well under a second.

Step 5: Run the Query for HPOS Stores (wp_wc_orders)

This is the part most existing tutorials skip entirely, and it’s the one that matters most if your store was set up any time from late 2023 onward.

With HPOS active, order status lives in a dedicated status column on the wp_wc_orders table. There’s no post_type filter and no wc- prefix confusion, but the column name and table are different from what older guides describe:

UPDATE wp_wc_orders
SET status = 'wc-completed'
WHERE status = 'wc-processing';

Notice the structure is simpler than the legacy version. HPOS was built with dedicated order tables specifically, so WooCommerce wouldn’t have to filter a shared wp_posts Table by post type on every query. That’s a large part of why HPOS-enabled stores handle bulk operations faster in general, not just for this one query.

One honest limitation: If your store runs HPOS with compatibility mode enabled, the legacy wp_posts table still exists as a synced backup copy. Updating only wp_wc_orders In that setup will eventually sync over, but not instantly.

If you need both tables aligned right away, you’d need to update both, which is an easy-to-mess-up scenario that makes the safer alternatives in Step 8 worth considering for anything beyond a one-time fix. WooCommerce’s own HPOS migration documentation covers how compatibility mode and synchronisation work if you want the full picture before deciding.

Step 6: Narrow the Query to Specific Orders

Updating every processing order at once is rarely what you want. Most of the time, you’re after a specific batch. Here’s how to scope it down.

By date range (legacy storage):

UPDATE wp_posts
SET post_status = 'wc-completed'
WHERE post_type = 'shop_order'
AND post_status = 'wc-processing'
AND post_date BETWEEN 'dd/mm/yy' AND 'dd/mm/yy';

By date range (HPOS):

UPDATE wp_wc_orders
SET status = 'wc-completed'
WHERE status = 'wc-processing'
AND date_created_gmt BETWEEN 'dd/mm/yy' AND 'dd/mm/yy';

By a specific list of order IDs (either system):

UPDATE wp_wc_orders
SET status = 'wc-completed'
WHERE id IN (1042, 1043, 1044, 1050);

Swap id for ID and the table name for wp_posts If you’re on legacy storage. Narrow scopes like this are safer for a reason: a typo in a broad WHERE Clause can complete orders that were never ready to ship.

If your store sells subscriptions alongside one-time products, add a type filter so renewal orders don’t get included in the same batch as regular purchases:

UPDATE wp_wc_orders
SET status = 'wc-completed'
WHERE status = 'wc-processing'
AND type = 'shop_order';

Subscription renewal orders often carry a different type value, and marking one complete before a shipment goes out can confuse both your fulfilment process and the customer’s order history.

Step 7: Verify the Update Worked

Don’t assume the query did what you expected. Check it.

1. Run a SELECT Count before you update, so you know your starting number.
   
2. Run the same SELECT with 'wc-completed' After that, you can confirm the count moved.
   
3. Spot-check two or three individual orders in WooCommerce > Orders to see the new status reflected in the admin screen.
   

SELECT COUNT(*) FROM wp_wc_orders WHERE status = 'wc-completed';

If the number matches what you expected, you’re done. If it’s off, stop and check your WHERE clause before running anything else.

A Quick Real-World Example

Here’s a situation that comes up often. A store runs a payment gateway integration that occasionally fails to fire its webhook, leaving a batch of paid orders stuck in “processing” for days. The customer already has their product. The store owner just needs the order record to reflect that.

Checking the gateway logs confirms the payment cleared for all of them. That’s the moment for Step 6’s date-range query, scoped to the exact day the webhook broke, rather than a blanket update across the whole store. Ten orders, one query, done in under a minute once the backup is in hand.

This is also the scenario where skipping the email matters least. The customer already knows their order went through. Direct SQL fits here better than WP-CLI would, since there’s nothing left to notify anyone about.

Step 8: What Direct SQL Skips (And Safer Alternatives)

Here’s the part that trips people up after the fact. A direct SQL update changes the status column and nothing else. It does not trigger WooCommerce’s own hooks.

That means:

• No order completion email goes to the customer
• Stock levels don’t get adjusted or released
• Any third-party integrations relying on order hooks never fire
• Analytics and reporting tables that WooCommerce keeps in sync separately won’t update automatically

For a one-time cleanup of a handful of stuck orders, that’s usually a fair trade for speed. For anything customer-facing or recurring, it’s worth using an approach that keeps everything in sync.

Here’s how the three approaches compare once you compare speed against what each one triggers:

Method	Speed	Triggers Hooks	Best For
Direct SQL	Fastest, milliseconds even at scale	No	One-time cleanup, no customer impact needed
WP-CLI	Fast, seconds for hundreds of orders	Yes	Bulk fixes where emails and stock still need to fire
PHP / update_status()	Slower per order, but can be automated	Yes	Automated workflows, custom plugins, scheduled jobs

WP-CLI, order by order:

wp wc shop_order update 1042 --status=completed --user=admin

This triggers the same hooks as a manual status change in the dashboard would, including the completion email.

PHP, for developers building this into a script or plugin:

$order = wc_get_order( $order_id );
$order->update_status( 'completed' );

Honestly, if you’re updating more than a few dozen orders on a regular basis, this is the version worth building instead of reaching for raw SQL every time. It costs a little more to set up and pays it back the first time a customer asks why they never got a shipping confirmation.

Step 9: Automate This for Recurring Cleanup

If orders stall in “processing” on a regular basis, running this query by hand every week gets old fast. A scheduled job handles it without you thinking about it.

1. Write the WP-CLI version of your query into a shell script.
   
2. Add a condition, such as only touching orders older than three days, so fresh orders aren’t included by mistake.
   
3. Schedule it with a scheduled task on your server, typically once daily:

wp wc shop_order list --status=processing --format=ids | xargs -I {} wp wc shop_order update {} --status=completed --user=admin

This pulls every processing order ID and updates each one through the same path a manual dashboard change would use, hooks included. Add a date filter to the list command if you only want orders past a certain age.

A word of caution here: automatic completion makes sense for virtual products and digital downloads, where there’s nothing left to ship. For physical goods, auto-completing before an item leaves the warehouse just hides a problem instead of fixing it.

Common Mistakes to Avoid

• Skipping the backup: This is the one mistake you can’t undo after the fact. Everything else on this list is recoverable if you have a recent backup. This one isn’t.
• Forgetting the wc- Prefix on legacy storage: The query runs, finds nothing, and you walk away thinking it worked.
• Assuming HPOS syntax works on a legacy store, or the reverse: Check Step 1 first, every time, even if you’re fairly sure you already know the answer.
• Running a broad WHERE clause without testing the SELECT version first: Read before you write. A five-second check saves a much longer cleanup.
• Not checking database connection settings before a scheduled bulk job: A dropped connection mid-query on a large batch can leave orders in an inconsistent state, some updated and some not.
• Ignoring compatibility mode: If both tables exist on your store, decide up front whether you need to update both or just the authoritative one.
• Auto-completing physical orders too early: Fine for downloads. Risky for anything that still needs to ship.

Conclusion

Marking orders complete in WooCommerce through MySQL comes down to knowing which table your store uses right now, backing up first, and running the right query for that system. Legacy stores update wp_posts HPOS stores update wp_wc_orders, and the two are not interchangeable.

For occasional bulk fixes, direct SQL is fast and gets the job done. For anything that needs customer emails or stock adjustments to fire correctly, WP-CLI or a small PHP script does the same job without the side effects you’d otherwise have to patch manually.

If your store handles a lot of order volume and this kind of cleanup keeps coming up, it might be worth pairing it with better order automation tools so fewer orders get stuck in processing in the first place. Some stores also add a dedicated WooCommerce order communication plugin to keep customers informed automatically once an order’s status changes, whether that change came from the dashboard or a script.

Not every store needs a script for this. If you’re only clearing out a handful of stuck orders once in a while, the manual query from Step 4 or Step 5 does the job on its own. Save the automation from Step 9 for when this becomes a weekly chore instead of an occasional fix. Whichever path fits, run the backup first. Nothing else on this list matters if that step gets skipped.

Frequently Asked Questions (FAQs)