Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Spot the SQL bug 10 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Uh... what's that first AND supposed to do?
  • 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/
    • You got it. I noticed there was a problem (caused by copy-and-paste) when I saw how many rows were updated. Fortunately it was easy enough to fix the rows that now had a state of '0'.
    • 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
  • ... of my late father, who always used to confuse "AND" with "OR". His reasoning went something like "I want all the cases where city='Los Angeles' and all those where city='New York'", translated into SQL as
    SELECT *
    FROM records
    WHERE city='Los Angeles'
      AND city='New York'
    which returned rather few results.
    • That pesky English language. I guess in pseudocode that would be
      SELECT *
      FROM records
      WHERE city='Los Angeles'
      AND ALSO SELECT *
      FROM records
      WHERE city='Los Angeles'
      I'm an SQL newbie, though, so maybe there is some kind of do-multiple-selects-at-once (intersecting selects?) syntax that I'm not familiar with.
      • How about:
        SELECT *
        FROM records
        WHERE city='Los Angeles'
        UNION ALL
        SELECT *
        FROM records
        WHERE city='New York'
        This works if your RDBMS supports UNION queries. AFAIK MySQL does this since 4.0.0.
        In this example I would not use this method. The city = 'New York' or city = 'Los Angeles' is much better.

        mark
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.