MySQL Console

Some good advice for modifying records is to follow these steps:

- Backup your database under: Admin > General
- Do a SELECT query to see what you are matching
- Then do a DELETE or UPDATE query

Your SQL would probably be something like this:

SELECT * FROM cms_yourTable WHERE phone LIKE "01803%"

UPDATE cms_yourTable SET phone = "" WHERE phone LIKE "01803%"

Hope that helps! And be careful! :)

Thanks Guys

Just one more question. How long would the query take to execute? I have used the code as below - with updates to the relevant fields and nothing seems to happen.

This is the query I am using. (Note: the phone number example I gave earlier was just a sample - what I actually am trying to do is remove answers to a competition. The table is cms_contacts. The answer I need to remove is "Tomato" and it is in a field called "answer"

Here is my query:

SELECT * FROM cms_contacts WHERE answer LIKE "Tomato%"
UPDATE cms_contacts SET answer = "" WHERE answer LIKE "Tomato%"

Thanks

Hi Jeff,

Usually it executes in a fraction of a second.

The first select query will show you what is matched, and you can also limit the columns returned to make it easier to see just answer:

SELECT answer FROM cms_contacts WHERE answer LIKE "Tomato%"

Then the update won't return anything (it might show records updated at the bottom).

Then run select again and it shouldn't return any results since you've removed "Tomato" from all those records.

Note that in LIKE queries % means "anything". So "Tomato%" means "Starts with Tomato and followed by anything else". If you wanted to match records with tomato anywhere in the string you'd use "%Tomato%".

Hope that helps!