Farooque Farooque - 1 month ago 5
SQL Question

Get just Inserted/Modified record based on updated_timestamp

I want to get just Inserted/Modified record based on updated_timestamp.

I have following scenario for

DB2
database:


  1. Triggering
    insert
    or
    update
    query to DB. The table contains updated_timestamp field which capture the insert or updated time.

  2. Want to get my previous inserted/ updated record only using
    select
    query.



Example

insert into table_name(x,y,CURRENT TIMESTAMP);


want to get the above inserted record using
select
as

select * from table_name where updated_timestamp > ?


with what value should I replace the
?
, above query should return me latest inserted record as
x,y,<time_stamp>

Answer

If I understand what your asking, couldn't you use a subquery pulling the max(updated_timestamp) and other values from the table and use that to filter to only the most recently updated records for each one?

Something like this:

insert into table_name (x, y, timestamp)
Select table_name.x, table_name.y, DateTime()
from table_name join (select x, y, Max(updated_timestamp) 
updated_timestamp from table_name) table_name2
on table_name.x = table_name2.x and table_name.y = tablename2.y
and table_name.updated_timestamp = table_name2.updated_timestamp