So I have two tables, a current table and a master table. The current table gets refreshed each week and at the end of the week, is copied to the master table to retain historical data. Say that I refresh the current table at the beginning of the week, and want to take the latest data from the Master table and update the current table with that data. The current table could have additional IDs or some of the IDs could have dropped off (These rows would not receive data from the master table). I would only want to update the rows in the current table that have existing data within the attr1,attr2,attr3 columns. A particular ID could have multiple records within the master table, I would only want the latest record to be used to update the current table. The data comes from another database where no direct link is possible so I must import the data weekly. Here are some create/insert statements:
create table current_T (ID1 varchar(100),adate date,attr1 varchar(100),attr2 varchar(100),attr3 varchar(100))
create table Master_T (ID1 varchar(100),adate date,attr1 varchar(100),attr2 varchar(100),attr3 varchar(100))
begin
insert into current_T (ID1,adate)
values ('IE111','08/02/13');
insert into current_T (ID1,adate)
values ('IE112','08/02/13');
insert into current_T (ID1,adate)
values ('IE113','08/02/13');
insert into master_T (ID1,adate,attr1,attr2,attr3)
values ('IE111','08/01/13','yes','abc','123');
insert into master_T (ID1,adate,attr1,attr2,attr3)
values ('IE112','08/01/13','no','dgf','951');
insert into master_T (ID1,adate,attr1,attr2,attr3)
values ('IE113','08/01/13','no','dgf','951');
insert into master_T (ID1,adate,attr1,attr2,attr3)
values ('IE113','07/01/13','no','dgf','951');
end;
This one's been a head scratcher for me and any help would be greatly appreciated. I'm coding in Apex 4.1
Thanks,
-Steve