During development and testing of your Magento based e-commerce website you do a lot of test orders. Before going live you want to delete all of them but unfortunately you have no option in Magento admin to delete the orders, you can only change the orders status to “Canceled”.

You can log in to phpMyAdmin or MySql console, select your database and then run the following queries to delete your orders in Magento.

NOTE: PLEASE MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THE BELOW QUERIES. In case your tables have a prefix then you need to prepend it to the table names in below queries.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE `sales_billing_agreement`;
TRUNCATE `sales_billing_agreement_order`;

TRUNCATE `sales_flat_order`;
TRUNCATE `sales_flat_order_address`;
TRUNCATE `sales_flat_order_grid`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sales_flat_order_payment`;
TRUNCATE `sales_flat_order_status_history`;

TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `sales_flat_quote_shipping_rate`;

TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;

TRUNCATE `sales_order_tax`;
TRUNCATE `sales_order_tax_item`;

TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;

TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;

TRUNCATE `sales_payment_transaction`;

TRUNCATE `sales_recurring_profile`;
TRUNCATE `sales_recurring_profile_order`;

TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;

TRUNCATE `log_quote`;
TRUNCATE `report_event`;

TRUNCATE `sales_invoiced_aggregated`;
TRUNCATE `sales_invoiced_aggregated_order`;
TRUNCATE `sales_order_aggregated_created`;
TRUNCATE `sales_order_aggregated_updated`;
TRUNCATE `sales_refunded_aggregated`;
TRUNCATE `sales_refunded_aggregated_order`;
TRUNCATE `sales_shipping_aggregated`;
TRUNCATE `sales_shipping_aggregated_order`;
TRUNCATE `sales_bestsellers_aggregated_daily`;
TRUNCATE `sales_bestsellers_aggregated_monthly`;
TRUNCATE `sales_bestsellers_aggregated_yearly`;
TRUNCATE `tax_order_aggregated_created`;
TRUNCATE `tax_order_aggregated_updated`;
TRUNCATE `coupon_aggregated`;
TRUNCATE `coupon_aggregated_order`;
TRUNCATE `coupon_aggregated_updated`;

ALTER TABLE `sales_billing_agreement` AUTO_INCREMENT=1;
ALTER TABLE `sales_billing_agreement_order` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_tax_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;

ALTER TABLE `sales_payment_transaction` AUTO_INCREMENT=1;

ALTER TABLE `sales_recurring_profile` AUTO_INCREMENT=1;
ALTER TABLE `sales_recurring_profile_order` AUTO_INCREMENT=1;

ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;

ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_aggregated_updated` AUTO_INCREMENT=1;
ALTER TABLE `sales_refunded_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_refunded_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipping_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_shipping_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_daily` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_monthly` AUTO_INCREMENT=1;
ALTER TABLE `sales_bestsellers_aggregated_yearly` AUTO_INCREMENT=1;
ALTER TABLE `tax_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `tax_order_aggregated_updated` AUTO_INCREMENT=1;
ALTER TABLE `coupon_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `coupon_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `coupon_aggregated_updated` AUTO_INCREMENT=1;

--
-- For Enterprise Edition Begins
--

TRUNCATE `enterprise_reward`;
TRUNCATE `enterprise_reward_history`;
TRUNCATE `enterprise_customer_sales_flat_order`;
TRUNCATE `enterprise_customer_sales_flat_order_address`;
TRUNCATE `enterprise_customer_sales_flat_quote`;
TRUNCATE `enterprise_customer_sales_flat_quote_address`;

ALTER TABLE `enterprise_reward` AUTO_INCREMENT=1;
ALTER TABLE `enterprise_reward_history` AUTO_INCREMENT=1;
ALTER TABLE `enterprise_customer_sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `enterprise_customer_sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `enterprise_customer_sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `enterprise_customer_sales_flat_order` AUTO_INCREMENT=1;

--
-- For Enterprise Edition Ends
--

TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Alternatively you can try Magento Connect extension “Seamless Delete Order”. It allows you to delete order from Magento admin, it adds a “Delete Order” option in the Actions dropdown on the Sales Order Grid Page. I have not tested this extension as I prefer to run the sql queries to delete the test orders.