Link tables and timestamp date

Database
Enthusiast

Link tables and timestamp date

In simple English I have 2 tables, I wish to compare them by date and any new records in table 1 to be appended to table 2.

For the sake of example Table1 has 2 fields Cust_Ref and load_date (timestamp format). While table2 has 3 fields Cust_Ref, Payment_Date and Amount

I need to get all records from Table1 where the load_date is greater than the Payment_Date from table2. (These will be the new records) However I do not know how to “link” the tables in that there is not a common field to join on. Whilst the field Cust_ref is in both tables surely by joining on it will not bring back any records that I need.

So my question is how do I create the join and will the fact that the date in table1 is in timestamp format cause a problem. Assuming we can solve these issues will I then be able to append the “new” records from table1 to table2 in one combined step or does it need to be done in several stages. (In table2 the Amount will always be £30 and the Payment_Date will always be the day the tables were compared)

Any help gratefully received.

Bob
2 REPLIES
Enthusiast

Re: Link tables and timestamp date

Hi, check this and say does it satisfy You

create table table1
(
Cust_Ref integer,
load_date timestamp
)
insert into table1 values(3,'2011-01-31 00:00:00');
insert into table1 values(4,'2011-02-22 00:00:00');
insert into table1 values(5,'2011-03-10 00:00:00');

create table table2
(
Cust_Ref integer,
Payment_Date date,
Amount decimal(15,2)
)
insert into table2 values(1,'2011-02-16',30);
insert into table2 values(2,'2011-02-16',30);

insert into table2(Cust_Ref,Payment_Date,Amount)
select
t1.Cust_Ref,
current_date,
30 as Amount
from
table1 t1
where
cast(load_date as date) > (
select
max(Payment_Date)
from
table2
);
Enthusiast

Re: Link tables and timestamp date

Perfect Kostek, thank you just what i needed