New to SQL: How can I search for & replace specific info?

Hello cP,

I have a question regarding how to create a database search and replace query.

I know how to use replace(field, ‘string to find’, ‘replace string’); but I am wondering how to use this when I have a search query instead of a string to find.

For instance, when I run:

Code:

SELECT * FROM `db`.`table`
WHERE ( 
  CONVERT( `column` USING utf8 ) 
  LIKE '%string_to_find%'
)

I get a huge list of rows from the table I'm searching in whose column contains my search string. But what I don't know how to do is replace a string of a different column of this same table using only these results.

So, for instance, if I had a database called "db" and its table called "table" contained the following data:

id	color	size
1	red	L
2	green	S
3	red	M

What query would I use to search for all matches of the color red, and then replace the size of only the rows that have the color red with the size XL?

Thanks!
BigMike

 

Hmmmmm, okay. I posted it here because this forum is described as "Everything database-related including database users, MySQL®, PostgreSQL, phpMyAdmin and phpPgAdmin.", and this is database related for MySQL.

Huusoku

 

Understood. Thanks for the clarification!

-Huusoku