Update Multiple columns in DB2v7

[garethjwelsh]

The problem i have is i am trying to update one table with values from another table. I used to do it using an Inner Join, but Db2 version 7 doesn't allow this. I know one way of doing it would be to do

Update tbl set field=(SELECT field from tbl2 where id=1) Where id = 1

Now i know this works, but we have about 100 fields in the table i am trying to update. I ws just wondernig if anyone knew of an alternative ways.
Another option is to bring all the fields into a Java recordset, assign the values to varibales and pass the variables to the update statement, but i don't know if this is quicker and more efficient, input would be most welcome.

 

[Dagon]

Why can't you just do:

Update tbl set (field1,field2) =(SELECT field1,field2 from tbl2 where id=1) Where id = 1

You could also use a correlated subquery:

Update tbl t1 set (field1,field2) =(SELECT field1,field2 from tbl2 t2 where t1.id=t2.id)
and exists
(SELECT 1 from tbl2 t2 where t1.id=t2.id)

You need the "exists" to avoid every row in the table being updated, even those with no match in tbl2. All this works fine in version 7. I have used it many, many times.