A bit of database help

[14Steve14]

I am needing a bit of help deleting some old text/comments from the orders_status_history table. Many years ago I had a setting wrong in PayPal and it led to lots, and I mean lots, of entries in this table that look like

PayPal IPN Verified [Completed (Verified; £6.72)]

I am now cleaning areas of the database, which is probably unnecessary, so that the whole database is smaller. I have deleted the rest of the message that was there as that was only long bits of text, but I am having problems with this last bit as it contains the order total, in the case of the quote below its the 1.99 bit. I have searched online to see if something similar has been asked but I am a bit lost. I can see wildcard mentioned but cannot find a way to use it to remove the order total figure. Everything in the other messages is exactly the same apart from the order total.

UPDATE orders_status_history SET comments = REPLACE(comments, 'PayPal IPN Verified [Completed (Verified; £1.99)]', ' ')

Is there a way to remove the text and the order total using some form of wildcard on just the last order total bit. It looks like these are several thousand entries like this and I would prefer not to do them all manually.

Any one able to offer some sort of advice.

[ecartz]

Code: Select all

UPDATE orders_status_history SET comments = '' WHERE comments LIKE 'PayPal IPN Verified [Completed (Verified; %)]'

You may want to select on that WHERE first to make sure that it's doing what you want and no more.

[14Steve14]

Code: Select all

UPDATE orders_status_history SET comments = '' WHERE comments LIKE 'PayPal IPN Verified [Completed (Verified; %)]'

You may want to select on that WHERE first to make sure that it's doing what you want and no more.

@ecartz Many thanks for that. I thought there would be a way, but sql stuff really confuses me, and as its doing something to the database I really need it to be right. I thought it would be the % after reading online about it, but had no idea how to use it.

I will give it a try on the test database first and let you know how it goes.

[14Steve14]

I will give it a try on the test database first and let you know how it goes.

It went very well. I have been able to get rid of all the silly messages from when I had a setting wrong in Paypal standard payments. Probably made no difference to the database of tyhe speed of it, but in my mind it is now a cleaner database.