Even More Troublesome Updates
To add even more to the discussion of correlated updates, there was a question on the SQLite mailing list last week (full post here) that said the following:I've been struggling with the following:
update table1 set column3 = (select i.colum3 from table2 i, table1 t
where t.column1 = i.column1 and t.column2 = i.column2)
Reading the manual and this list I learned that this statement will
grab the first result of my sub-select and populate it in every row
The ever-helpful Dennis Cote provides the following explanation:
Your sub query can be completely evaluated regardless of the currently
active row in the outer update scan, and so it is. Your query does not
depend upon the values in table1 row being updated. The entire sub query
is evaluated once, and the value of column3 from the first result row is
returned as the value of the sub query expression, and then used to
update every row in table1.
To achieve the desire effect, the query must be rewritten as (stolen from Dennis' post):
UPDATE table1
SET column3 = (
SELECT column3
FROM table2
WHERE table2.column1 = table1.column1
AND table2.column2 = table1.column2)
This works as desired because it relates the target table with the table being queried.
Who'da-thunk that UPDATEs would be so troublesome? Go figure.
You Learn Something New Every Day
Just when I think I've heard of every database system on the planet... I find Hitachi HiRDB. Go figure. I wonder if I'll be able to learn anything about it...A Test Data Home Run
Need a small set of real-world test data to work with? Just interested in baseball statistics (like me)? Check out Sean Lahman's baseball statistics database. Fun stuff.Beware Quoted Identifiers
I ran into a gotcha today that I'd forgotten about - quoted identifiers in DB2. If an object name in an SQL statement isn't quoted, DB2 converts the name to all upper case; if it is quoted, it is left untouched. The catch is that all object names are compared in a case-sensitive manner. DB2 generally appears to treat things as case-insensitive if you don't quote object names, which trips people up when they do quote object names.For example, try the following using your DB2 query tool of choice:
CREATE TABLE "ATest"(x int)
INSERT INTO atest VALUES(1)
and you'll find that the INSERT fails - there's no table named "ATEST" in the system catalogs, only one named "ATest".
The lesson here: if you're going to use quoted identifiers in DB2, you always have to use quoted identifiers in DB2. Don't mix things up.
UPDATE: Turns out this is an issue in Oracle too... go figure. I'm starting to wonder if this is standard behavior... we'll see.
