Ndtyjky

The above answer is too old and there is drawback of this answer that it doesn't empty payment tables so there is a chance of legacy.
you can use below code it will remove payment information also.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE `sales_flat_creditmemo`;

TRUNCATE `sales_flat_creditmemo_comment`;

TRUNCATE `sales_flat_creditmemo_grid`;

TRUNCATE `sales_flat_creditmemo_item`;

TRUNCATE `sales_flat_invoice`;

TRUNCATE `sales_flat_invoice_comment`;

TRUNCATE `sales_flat_invoice_grid`;

TRUNCATE `sales_flat_invoice_item`;

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_invoiced_aggregated`;

TRUNCATE `sales_invoiced_aggregated_order`;

TRUNCATE `sales_order_aggregated_created`;

TRUNCATE `sendfriend_log`;

TRUNCATE `tag`;

TRUNCATE `tag_relation`;

TRUNCATE `tag_summary`;

TRUNCATE `wishlist`;

TRUNCATE `log_quote`;

TRUNCATE `report_event`;

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_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_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_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 `sendfriend_log` AUTO_INCREMENT=1;

ALTER TABLE `tag` AUTO_INCREMENT=1;

ALTER TABLE `tag_relation` AUTO_INCREMENT=1;

ALTER TABLE `tag_summary` AUTO_INCREMENT=1;

ALTER TABLE `wishlist` AUTO_INCREMENT=1;

ALTER TABLE `log_quote` AUTO_INCREMENT=1;

ALTER TABLE `report_event` AUTO_INCREMENT=1;



SET FOREIGN_KEY_CHECKS=1; 

Take a look at How to delete orders from Magento

<?php

$mageFilename = 'app/Mage.php';

require_once $mageFilename;

Varien_Profiler::enable();

Mage::setIsDeveloperMode(true);

ini_set('display_errors', 1);

umask(0);

Mage::app('default');

Mage::register('isSecureArea', 1);

//until here you gained access to the Magento models. The rest is custom code



$orderId = 156;//put here the id of the order you want to delete. THE ONE FROM THE DATABASE NOT THE INCREMENT_ID



$order = Mage::getModel('sales/order')->load($orderId);



$invoices = $order->getInvoiceCollection();

foreach ($invoices as $invoice){

   $invoice->delete();

}



$creditnotes = $order->getCreditmemosCollection();

foreach ($creditnotes as $creditnote){

   $creditnote->delete();

}



$shipments = $order->getShipmentsCollection();

foreach ($shipments as $shipment){

   $shipment->delete();

}



$order->delete();

?>

I setup a repository for maintaining simple SQL scripts to run before launching a new Magento site. So far they can help:

1. empty test data from Mage; orders, customers, reports, logs etc.


2. empty test data from various extensions


3. set desired IDs for next order, invoice, shipment and creditmemos

https://github.com/ccondrup/mage-reset
Help improve them!

I created an open source commandline tool

you can use it like this:

#delete order by increment id

php -f shell/delete-order.php -- --increment-id 100000001



#test delete order by increment id (wont actually delete it)

php -f shell/delete-order.php -- --dry-run --order-id 32

https://github.com/mauricioprado00/magento-delete-order/releases

Almost all the previous answers have problem with Foreign key Constraint or wrong table names. I was able to achieve the deletion on a Magento v 1.9.1.0 CE using the SQL below, I modified the SQL given in previous answers:

    SET FOREIGN_KEY_CHECKS=0;

TRUNCATE `mg_sales_flat_creditmemo`;

TRUNCATE `mg_sales_flat_creditmemo_comment`;

TRUNCATE `mg_sales_flat_creditmemo_grid`;

TRUNCATE `mg_sales_flat_creditmemo_item`;

TRUNCATE `mg_sales_flat_invoice`;

TRUNCATE `mg_sales_flat_invoice_comment`;

TRUNCATE `mg_sales_flat_invoice_grid`;

TRUNCATE `mg_sales_flat_invoice_item`;

TRUNCATE `mg_sales_flat_order`;

TRUNCATE `mg_sales_flat_order_address`;

TRUNCATE `mg_sales_flat_order_grid`;

TRUNCATE `mg_sales_flat_order_item`;

TRUNCATE `mg_sales_flat_order_payment`;

TRUNCATE `mg_sales_flat_order_status_history`;

TRUNCATE `mg_sales_flat_quote`;

TRUNCATE `mg_sales_flat_quote_address`;

TRUNCATE `mg_sales_flat_quote_address_item`;

TRUNCATE `mg_sales_flat_quote_item`;

TRUNCATE `mg_sales_flat_quote_item_option`;

TRUNCATE `mg_sales_flat_quote_payment`;

TRUNCATE `mg_sales_flat_quote_shipping_rate`;

TRUNCATE `mg_sales_flat_shipment`;

TRUNCATE `mg_sales_flat_shipment_comment`;

TRUNCATE `mg_sales_flat_shipment_grid`;

TRUNCATE `mg_sales_flat_shipment_item`;

TRUNCATE `mg_sales_flat_shipment_track`;

TRUNCATE `mg_sales_invoiced_aggregated`;

TRUNCATE `mg_sales_invoiced_aggregated_order`;

TRUNCATE `mg_sales_order_aggregated_created`;

TRUNCATE `mg_sendfriend_log`;

TRUNCATE `mg_tag`;

TRUNCATE `mg_tag_relation`;

TRUNCATE `mg_tag_summary`;

TRUNCATE `mg_wishlist`;

TRUNCATE `mg_log_quote`;

TRUNCATE `mg_report_event`;

ALTER TABLE `mg_sales_flat_creditmemo` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_creditmemo_comment` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_creditmemo_grid` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_creditmemo_item` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_invoice` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_invoice_comment` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_invoice_grid` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_invoice_item` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_order` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_order_address` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_order_grid` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_order_item` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_order_payment` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_order_status_history` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote_address` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote_address_item` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote_item` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote_item_option` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote_payment` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_shipment` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_shipment_comment` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_shipment_grid` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_shipment_item` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_flat_shipment_track` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_invoiced_aggregated` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_invoiced_aggregated_order` AUTO_INCREMENT=1;

ALTER TABLE `mg_sales_order_aggregated_created` AUTO_INCREMENT=1;

ALTER TABLE `mg_sendfriend_log` AUTO_INCREMENT=1;

ALTER TABLE `mg_tag` AUTO_INCREMENT=1;

ALTER TABLE `mg_tag_relation` AUTO_INCREMENT=1;

ALTER TABLE `mg_tag_summary` AUTO_INCREMENT=1;

ALTER TABLE `mg_wishlist` AUTO_INCREMENT=1;

ALTER TABLE `mg_log_quote` AUTO_INCREMENT=1;

ALTER TABLE `mg_report_event` AUTO_INCREMENT=1;



SET FOREIGN_KEY_CHECKS=1; 

Remember to change mg_ to your table prefix.

Create a file in the root directory and paste the following code.

It will remove all the orders from admin grid!



<?php



/**

 * @author Dejan Radic <dejan.radic@inchoo.net>

 */



if (version_compare(phpversion(), '5.2.0', '<')===true) {

    echo  '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';

    exit;

}



error_reporting(E_ALL | E_STRICT);

ini_set('display_errors', 1);



$mageFilename = 'app/Mage.php';



if (!file_exists($mageFilename)) {

    echo $mageFilename." was not found";

    exit;

}



require_once $mageFilename;



Mage::app();



$executionPath = null;



/*

 * determine Magento Edition

 */

if (file_exists('LICENSE_EE.txt')) {

    $edition = 'EE';

}elseif (file_exists('LICENSE_PRO.html')) {

    $edition = 'PE';

} else {

    $edition = 'CE';    

}



if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)

        || ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)

        || ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)

  ){

   $executionPath = 'old'; 

} else {

   $executionPath = 'new';  

}



$xpathEntity = 'global/models/sales_entity/entities//table';



if ($executionPath == 'old') {

    $xpathResource = 'global/models/sales_mysql4/entities//table';

} else {

    $xpathResource = 'global/models/sales_resource/entities//table';

}



$salesEntitiesConf = array_merge(

    Mage::getSingleton('core/config')->init()->getXpath($xpathEntity), 

    Mage::getSingleton('core/config')->init()->getXpath($xpathResource)

);



$resource = Mage::getSingleton('core/resource');

$connection = $resource->getConnection('core_write');





/*

 * If you want delete System/Order Statuses (Status and State) you

 * should comments below lines (46-51)

 */

$skipTables = array (

        $resource->getTableName('sales_order_status'),

        $resource->getTableName('sales_order_status_state'),

        $resource->getTableName('sales_order_status_label')

    );

$salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);





/*



Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+

    http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf



2.2. Adapters:



... The new Varien_DB_Adapter_Interface was added to sign a contract that all 

developed adapters must execute in order to get Magento working on an actual 

database. The interface describes the list of methods and constants that can be used by resource models...



Used below in the loop:



 * If $executionPath == 'old'

    * Varien_Db_Adapter_Pdo_Mysql::showTableStatus()

    * Varien_Db_Adapter_Pdo_Mysql::truncate()  

 * Else

    * Varien_Db_Adapter_Interface::isTableExists()

    * Varien_Db_Adapter_Interface::truncateTable()



*/



while ($table = current($salesEntitiesConf) ){

    $table = $resource->getTableName($table);



    if ($executionPath == 'old') {

        $isTableExists = $connection->showTableStatus($table);

    } else {

        $isTableExists = $connection->isTableExists($table);

    }

    if ($isTableExists) {

        try {

            if ($executionPath == 'old') {

                $connection->truncate($table);

            } else {

                $connection->truncateTable($table);

            }



            printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);

        } catch(Exception $e) {

            printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);

        }

    }



    next($salesEntitiesConf);

}



exit('All done...');



?>

Try this one from Inchoo:

Delete test orders in Magento

With this extension, not only it will delete the order(s), invoice(s), shipment(s), credit memo(s), RMA(s), but also it will revert back all the information such as collected tax, deleted products back to inventory and reset Increment IDs.

If you make a mistake when creating the order/invoice/shipment/credit memo/RMA and want to delete it, then this extension can do very well.

There is a test site where you can try it first and see if it works for you or not.

http://www.magentocommerce.com/magento-connect/ultimate-delete-order.html

Simply got your database phpMyadmin and then open the table sales_flat_order and then simply remove tha data from this tabel and your test orders will be cleared. No need of any code or commands. cool.