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):
SET column3 = (
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.
Hitachi HiRDB. Go figure. I wonder if I'll be able to learn anything about it... Sean Lahman's baseball statistics database. Fun stuff.
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. SQLzoo - something I stumbled upon yesterday. Not something I need every day, but could certainly be useful at times, especially for database neophytes.