Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    36

    Wink Unanswered: UPDATE query on a subset

    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,' ',' ')
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE table_name
       SET column_name = REPLACE(column_name,' ',' ')
     WHERE column_name LIKE '% %'
       AND anothercolumn = 100
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    36
    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?
    Last edited by Leafgreen; 03-23-11 at 23:46.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •