use Perl

• Just making it a bit clearer than the first reply. (Score:1)

  by neuroball (3260)

  Replace the first AND with a comma and everything should be okay. Like so:
  
  

  UPDATE users
  SET state = 'DC', city = 'Washington'
  WHERE state = 'DC'
  AND city = 'Washington'

  
  Did just stumble about this around a week ago ;)
  /oliver/
  • Re:Just making it a bit clearer than the first rep (Score:1)

    by Wallie (4036)

    AFAIK nothing will change when you execute this query. The values in the WHERE en in the SET parts are equal so it will only update rows where the case is already corect.
    
    You might want to do something with TO_LOWER() (in case of Oracle) in the WHERE clause. This will cause any indexes on the STATE and CITY column to be ignored (unless you used function-based indexes ).
    
    UPDATE users
SET state = 'DC', city = 'Washington'
WHERE TO_LOWER(state) = 'dc'
AND TO_LOWER(city) = 'washington'

    mark
    • Re:Just making it a bit clearer than the first rep (Score:2)

      by vsergu (505)

      I'm using MySQL, so the comparisons are case-insensitive (it depends on how the columns are defined). The query worked fine once I'd fixed the "AND" problem.
      • Re:Just making it a bit clearer than the first rep (Score:1)

        by Wallie (4036) <useperl@vanderwal.xs4all.nl> on 2003.12.02 9:16 (#26232)

        I didn't know that. It's been a while since I looked at MySQL. I'm waiting for a stable release that supports subqueries, at that point I'm going to look at it more closely.
        
        I'm not sure if I like the idea of case-insensitive columns (especially for names). But that's my personal opinion and it's configurable so I can always turn case-sensitivity on :-)
        
        mark

        Reply to This

        Parent