update DB2 table with values from another table

Situation Text to be written later.

UPDATE TABLE01 T1
SET (T1.FIELD01, T1.FIELD02)
=
(SELECT CROSS_FIELD_01, CROSS_FIELD02
FROM TABLE02 T2
WHERE T2.MATCHING_FIELD = T1.MATCHING_FIELD
)

End of code text

Find the difference between to Time fields DB2

Whether you have a character field holding time data (e.g. 10:45 PM) or you an actual time field (i.e. 10:45:00) and you want to find out the difference in minutes or hours between the two, you probably already know that you cannot use timestampdiff(), not without some modifications anyway.

In order to successfully extract the time difference, you need to convert your time into a timestamp. If the times we are comparing are in the same day, then you can use the current date. Example:

select timestampdiff(4,char(
timestamp(cast(current date as varchar(10)) || '-' || cast(cast('10:50 PM' as time) as varchar(10)))
-
timestamp(cast(current date as varchar(10)) || '-' || cast(cast('10:50 AM' as time) as varchar(10)))
)) AS MINUTES
from sysibm.sysdummy1

If you already have a date field with either an actual date only value or a character field with a date formatted YYYY-MM-DD, then you can replace “current date” with that date field value. Provided your field holding “time” properly casts into a time value, you can replace the string values (’10:50 PM’, ’10:50 AM’) with your time fields.

If your comparing the min and max of one or more fields, you can simply change the above to:

select timestampdiff(4,char(
max(timestamp(cast(current date as varchar(10)) || '-' || cast(cast(tbl.field as time) as varchar(10))))
-
min(timestamp(cast(current date as varchar(10)) || '-' || cast(cast(tbl.field as time) as varchar(10))))
)) AS MINUTES
from tbl

Comments and questions are welcome. 🙂

DB2 UPDATE with INNER JOINS

Unlike SQL Server, DB2 does not allow joins within its update statement.  For example

UPDATE T1
SET T1.COL1 = 'NEW VALUE'
FROM TABLE01 T1
INNER JOIN TABLE02 T2
  ON T1.KEYCOL = T2.KEYCOL
INNER JOIN TABLE03 T3
  ON T2.KEY2COL = T3.KEYCOL
WHERE T1.COL2 = 'SOME VALUE'

To accomplish the same thing in DB2 you would have to do the following:

UPDATE TABLE01 T
SET T.COL = 'NEW VALUE'
WHERE EXISTS
(
  SELECT *
  FROM TABLE01 T1
  INNER JOIN TABLE02 T2
   ON T1.KEYCOL = T2.KEYCOL
  INNER JOIN TABLE03 T3
   ON T2.KEY2COL = T3.KEYCOL
  WHERE T1.COL2 = 'SOME VALUE'
  AND T.KEYCOL = T1.KEYCOL
)

Essentially you write the query that would produce the rows that you want to update. Nest that query and execute a simple update statement with an EXISTS clause