SQL query update need help

[Ramses]

Hi, I need help with a sql query.
In field7 are random entries like

blue dog, yellow flowers, green, black

or

blue cat, yellow flowers, green, orange, flowers

Now I just want to remove the entry "yellow flowers" from all fields.
It must be something like
UPDATE customfields_entries SET field7 = '' where field7 = 'yellow flowers,'
But I'm not sure if this really works, any help woudl be great, thank you.

 

[rainmotorsports]

It's best done in code. You can clear the whole field but not keep part of it. Really easy to write a PHP script to do it.

To clear the field where an entry is found you will need to use a LIKE query.

 

[Ramses]

It's best done in code. You can clear the whole field but not keep part of it. Really easy to write a PHP script to do it.

To clear the field where an entry is found you will need to use a LIKE query.

Thanks, but writing a php script is beyond my knowledge, isn't there really no way to do it with a sql query?

 

[rainmotorsports]

Googling I would say look into the MySQL replace function. There is some powerful stuff in there but at this level it's almost like writing php.

Traditionally the issue is you need to get the current value. Make your change and then set it. Basic sql doesn't do that.

 

[AndyB]

I just want to remove the entry "yellow flowers" from all fields

How many fields have this value that require updating? I ask because if only say a couple hundred or less just do it manually.

 

[Ramses]

How many fields have this value that require updating? I ask because if only say a couple hundred or less just do it manually.

Yes, right now I'm doing it by hand, around 500 left and 500 done....

 

[Mouth]

Now I just want to remove the entry "yellow flowers" from all fields.
It must be something like
UPDATE customfields_entries SET field7 = '' where field7 = 'yellow flowers,'
But I'm not sure if this really works, any help woudl be great, thank you.

update customfields_entries
set field7 = replace(field7, 'yellow flowers', '')
where field7 like '%yellow flowers%'