UPDATE query on a subset

Leafgreen · #1 (**permalink**) 03-23-11, 21:37

I want to use the UPDATE command on a subset of rows in a table.

I know

Code:

UPDATE table_name
   SET table_name = REPLACE(column_name,'&nbsp;',' ')

will apply to all rows containing   in column_name and table_name. However, I only want the query to apply to a subset where another column = '100'. What is the query, please?

TIA

r937 · #2 (**permalink**) 03-23-11, 22:31

Code:

UPDATE table_name
   SET column_name = REPLACE(column_name,'&nbsp;',' ')
 WHERE column_name LIKE '%&nbsp;%'
   AND anothercolumn = 100

Leafgreen · #3 (**permalink**) 03-23-11, 22:42

cool! I've learned a lot from you, Rudy.

Why enclose   in the % characters? And only in the LIKE command and not the REPLACE command?

r937 · #4 (**permalink**) 03-23-11, 22:53

the percent signs are wildcard characters, and they are used only with LIKE

the reason for adding that particular condition to the WHERE clause is to avoid attempting to update rows which don't include the non-breaking space

according to the manual...

Quote:

If you set a column to the value it currently has, MySQL notices this and doesn't update it.

so if a certain row has a column value which doesn't contain the non-breaking space, then REPLACE(column_name,' ',' ') doesn't actually replace anything, so the result of the REPLACE function is the same as what the column value originally was, and mysql apparently doesn't actually perform the update -- note that if it did go ahead and perform the update anyway, it would simply set the value to what it originally was

so i add the condition to circumvent not only the non-updating of a value to itelf, but also the unnecessary checking, which will speed up the query

Internet Services

Web Development

Digital Marketing

Consumer Tech