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

[Huusoku]

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:

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

 

[Infopro]

Not sure these forums are the best place to ask this sort of question as it's not exactly cPanel related.

Maybe these links will get you going in the right direction:
MySQL :: MySQL 5.0 Reference Manual :: 12.2.10 UPDATE Syntax
PHP MySQL Update
MySQL Update Query | MySQL Tutorials

GL!

 

[Huusoku]

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

 

[cPanelTristan]

Everything database related in regards to cPanel itself. This would be like posting development questions in the Developer Discussions area that were general coding questions for PHP or Perl when they aren't related to cPanel. The intended focus of this forum is cPanel-based questions.

General MySQL queries are a MySQL subject and best posted on the MySQL site itself if you cannot find out the information from an online search. The reason we'd suggest using that path is that most people on this forum will not know the information to answer the question.

 

[Huusoku]

Understood. Thanks for the clarification!

-Huusoku