Great post.

Would it be possible to delete specific orders? Say for instance, if your store had been running for a little while with the original test orders still in place and you only found the above snippet now?

Cheers,
Matt

Hey Tomislav Bilic,

In the Dashboard, if u see Lifetime Sales, this show the value of urs sales yet =//

The one attribute this code doesn’t seem to address is Shopping Cart Reports.

After running this code, if you visit backend and go to REPORTS>SHOPPING CART>PRODUCTS IN CARTS, you will see no items listed, but the counter will still list the number of test orders you’ve placed in carts.

I’d suggest the fix myself, but am unsure which table or attribute controls this value – should just be a matter of adding two lines – one to truncate, and another to auto-increment.

Oh, and BTW, don’t worry about the PRODUCTS IN CARTS tally being wrong. It seems to be unrelated.

It looks like the wrong reporting of the total in REPORTS>SHOPPING CART>PRODUCTS IN CARTS is an issue with the current version of Magento (at least as of 1.1.6), unrelated to the above queries. I think they may have grabbed the total from the wrong table(s) in the first place. Someone could test in a fresh install with a bunch of products loaded, but no items ever added to carts.

This was pointed out in the Magento Forums, and from my research in code and DB queries, it seems to be the case.

See:

http://www.magentocommerce.com/bug-tracking/issue?issue=3899

Thanks Tomislav!!
It worked perfectly

Cheers!

Is this SQL Query still valid for Magento? new Log_Visitor tables got added in version 1.3.1.1, so I don’t this this query will still do it all 100%, it certainly removes the test orders in 1.3.2.2. Just don’t want it missing anything.

Please take a little look.
Thanks,
Rob.

trouble is that quite often post launch there are multiple test orders created, these then are un deletable

Thanks mate! Works perfect in magento 1.3.2.2!
No error in the reports > products in chart either!

Perfect

Well check this, a total solution for deleting Magento order “Delete Any Order”

https://www.yireo.com/software/delete-any-order

I don’t want to delete customers from magento. Can I leave the customer reset portion out and be okay?

thanks..

Also just tested on v1.3.2.3 and worked like a charm!
Thanks for this.

Did it with version 1.3.2.4 and it worked like a champ! Thanks for the solution.

Magento won’t let you delete orders or do a bunch of other stuff in its misguided attempt be your nanny in helping you to obey various accounting laws in certain countries.

I wish there was a “scofflaw” switch you could se that lets you do this forbidden stuff. Those of us in the United States are stuck with the inconveniences of a superset of every piddling regulation of every country in the EU.

yes how about this?

”
Matthew Mucklo Says

November 11th, 2008 at 23:10

Correct me if I’m wrong, but don’t you want to add these tables too?

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `log_url_info`;
TRUNCATE `log_url`;

ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;
“

I too am using 1.3.2.4. I tried the reset script and got a “Table Reset Successful” message, however when I return to the dashboard all of the dummy orders are still there. Any ideas what may have gone wrong, and why it did not clear the dummy orders? Thanks.

Brilliant, thanks

Brilliant post, thanks.

One point I would like to make though, just for others using this, if you don’t want product tags deleted etc, remove that line before running this in your db.

Is still valid in magento 1.4?

Has anyone tried this with release 1.4.0.1 ?

I delete specific orders like this (cobbed together from magento forums)

first, set collation with this script:
$value)
{
mysql_query(“ALTER TABLE $value COLLATE $db_collate”);
echo $value.’ changed to ‘.$db_collate.’ ‘;
}
}
echo “DB collation changed”;
?>

Then I run this in phpmyAdmin (just change the order ID at the start):
[it does seem to mess with the order ID in the URLs]

## SET FOREIGN_KEY_CHECKS = 1;
## WORKS ON VERSION 1.3.2.3

SET @orderId = ’100000772′;

SET FOREIGN_KEY_CHECKS = 1;

SET @salesFlatQuoteId = (
SELECT entity_id
FROM sales_flat_quote
WHERE reserved_order_id = @orderId
);
SET @salesOrderId = (
SELECT entity_id
FROM sales_order
WHERE increment_id = @orderId
);

CREATE TEMPORARY TABLE del_sales(
id INT AUTO_INCREMENT PRIMARY KEY,
salesId INT(10)
)
COLLATE ‘utf8_unicode_ci’;

CREATE TEMPORARY TABLE del_statusSales(
id INT AUTO_INCREMENT PRIMARY KEY,
salesId INT(10)
)
COLLATE ‘utf8_unicode_ci’;

INSERT INTO del_statusSales (salesId)
SELECT entity_id
FROM sales_order_entity_int
WHERE value = @salesOrderId
AND attribute_id = ANY (
SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = ‘order_id’
)
AND entity_id = ANY (
SELECT entity_id
FROM sales_order_entity
WHERE entity_type_id = ANY (
SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = ‘invoice’
OR entity_type_code = ‘shipment’
OR entity_type_code = ‘creditmemo’
)
);

INSERT INTO del_sales (salesId)
SELECT entity_id
FROM sales_order_entity
WHERE parent_id = ANY (
SELECT salesId
FROM del_statusSales
)
AND entity_type_id = ANY (
SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = ‘invoice_item’
OR entity_type_code = ‘invoice_comment’
OR entity_type_code = ‘shipment_item’
OR entity_type_code = ‘shipment_comment’
OR entity_type_code = ‘shipment_track’
OR entity_type_code = ‘creditmemo_item’
OR entity_type_code = ‘creditmemo_comment’
);
INSERT INTO del_sales (salesId)
SELECT salesId
FROM del_statusSales;

INSERT INTO del_sales (salesId)
SELECT entity_id
FROM sales_order_entity
WHERE parent_id = @salesOrderId;

DELETE FROM sales_order_entity
WHERE entity_id = ANY (
SELECT salesId
FROM del_sales
);

DELETE FROM sales_flat_quote
WHERE reserved_order_id = @orderId;

DELETE FROM sales_flat_order_item
WHERE quote_item_id = @salesFlatQuoteId;

DELETE FROM sales_order
WHERE increment_id = @orderId;

/* drop temp tables */
DROP TEMPORARY TABLE del_sales;
DROP TEMPORARY TABLE del_statusSales;

great job. thanks

Is there any way for doing this in magento 1.4.1? Thanks ~

This is not working on Recent Magento v1.4.1
Help me~~~~

Because Magento uses foreign keys, it’s enaugh to run truncate on 2-3 tables `sales_flat_order` , `sales_flat_quote` (for 1.4.1.0 didn’t test to see if there are any but tables as sales_flat_order_… will also be truncated) also truncate will set your increment id to 1