Set all Processing orders to Delivered? Or delete all orders?

[Brian1234]

Hi,

Is there an easy way to set all orders with 'Processing' status to 'Delivered'? I have a hundred-some and one-by-one would be a pain. I might be able to handle some SQL code if that's the way.

Alternately, can I delete all existing orders easily? We run an annual event and all were delivered last summer (the 100-some are the ones at will-call).

Or maybe I can add a 5th status 'History' and set all orders to that? I'd really like this solution but if it's too much trouble I'll be happy with one of the above.

Thanks!

[Brian1234]

I figured out how to add the 5th order status, History, but am still wondering about a good way to do a bulk change to that status. Any tips appreciated!

[Brian1234]

In case it helps someone later, I believe I did this correctly with an SQL query in PHPMyAdmin:

UPDATE orders SET orders_status=3 WHERE orders_status=2

In that sample, all orders records with the orders_status of 2 (Processing) have their orders_status field changed to 3 (Delivered).

[DrByte]

In case it helps someone later, I believe I did this correctly with an SQL query in PHPMyAdmin:

UPDATE orders SET orders_status=3 WHERE orders_status=2

In that sample, all orders records with the orders_status of 2 (Processing) have their orders_status field changed to 3 (Delivered).

And, by so-doing, you've now corrupted all your order data because there is no corresponding order_status_history record to match and validate the changes you've made to the raw data. Thus, the orders will no longer show correctly in your admin area, and may not display consistently in the customers' My Account area.

[Brian1234]

augh! I did make a backup first though.

So back to the original question. How to properly change order status in bulk (or just properly delete all existing orders, we really don't need the orders from 2008 in there!) ?

[DrByte]

To properly update the order status, one must not only update the orders table to the new status, but also add a new record to the orders_status_history table for *each* record, showing the new status which you've updated the orders table record to have.

There is no built-in automated method of that in v1.3.8.
Same with purging old orders.

You could explore whether there might be some addons that offer bulk updating or purging. However, those would likely also alter many core files to add other additional functionality also, which may or may not be something you wish to complicate your site with.

[Brian1234]

Yes, I gathered that, but I was hoping for more info on 'how to'. Can you point me to any particularly good threads that discuss how to properly change orders_status and order_status_updates to match? If there's more to it than those, I guess I'll be doing 130+ manually.

[DrByte]

you simply add a NEW order-status-history record for each order you are updating

[Brian1234]

OK, thanks! Would you take a look and see if this looks appropriate for zen-cart?

INSERT INTO orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments)
VALUES ($id, 5, '2010-03-03 23:00:00', 0, 'order status was changed to History by Brian')

There, $id represents the number of each actual order I have changed the status of. The rest of the fields would be static values exactly as shown where 5 is my custom History status, then that datetime, notification value and comment.

I'm not touching orders_status_history_id in the statement because it's an auto_increment and I think it will handle itself. I'm seeing some indexes (PRIMARY and idx_orders_id_status_id_zen) but am unsure if I have to do anything with those.

[DrByte]

That should be fine.