All the Perl that's Practical to Extract and Report

Spot the SQL bug

vsergu on 2003-12-01T19:04:36

Trying to fix case:

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

Re: Spot the SQL bug

dws on 2003-12-01T19:52:21

Uh... what's that first AND supposed to do?

Just making it a bit clearer than the first reply.

neuroball on 2003-12-01T20:37:26

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

vsergu on 2003-12-01T21:13:09

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'.

Re:Just making it a bit clearer than the first rep

Wallie on 2003-12-02T12:03:08

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

vsergu on 2003-12-02T13:54:46

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

Wallie on 2003-12-02T14:16:18

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

That reminds me...

bart on 2003-12-02T08:13:00

... 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.

Re:That reminds me...

hex on 2003-12-02T12:54:23

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.

Re:That reminds me...

Wallie on 2003-12-02T14:09:51

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

Re:That reminds me...

hex on 2003-12-02T14:17:56

Yeah, it is, and clearer as well. Interesting to know, though.