I'm an iWay Data Migrator specialist, trying to something DM can't do. Gotta get down to Teradata SQL. I'm trying to update the To_dt of the 1st B record with value of the From_dt in the 2nd B record (the Max From_dt per KEY). I'm hoping to accomplish it without a recursive Join. It seems I could get there with only a subselect of some type.
From this To this ------------------- ------------------- Key From_dt To_dt Key From_dt To_dt A Dy1 Dy2 -No Change A Dy2 Dy99 -No Change B Dy1 Dy99 ------> B Dy1 Dy2 B Dy2 Dy99 -No Change C Dy2 Dy99 -No Change
This is as close as I can come, but it doesn't like my first SELECT at all!
UPDATE tblname SET To_DT = (SELECT MAX(From_dt) FROM tblname GROUP BY Key ) WHERE From_dt IN (SELECT MIN(From_dt) FROM tblname GROUP BY Key)
I don't know if you can avoid having to join to some data set as you have to compare the key values to get the right date to use. I set up the following example:
create table jklee.test_dates (key_cd char(1) not null, from_dt integer not null, to_dt integer not null) unique primary index (key_cd, from_dt) ;
insert into jklee.test_dates values('A',1,2); insert into jklee.test_dates values('A',2,99); insert into jklee.test_dates values('B',1,99); insert into jklee.test_dates values('B',2,99); insert into jklee.test_dates values('C',2,99);
select * from jklee.test_dates key_cd from_dt to_dt B 1 99 C 2 99 A 2 99 A 1 2 B 2 99
I translated your query to somethign Teradata would run (TD doesn't like the update..set = (select..) format) The straight translation fo your query is:
update jklee.test_dates from (select max(from_dt) as max_from_dt from jklee.test_dates group by key_cd) as src_data set to_dt = src_data.max_from_dt where from_dt in (select min(from_dt) from jklee.test_dates group by key_cd) ;
but, that gives an update from multiple rows error.
I think you want something closer to: update jklee.test_dates from (select key_cd , max(from_dt) as max_from_dt , min(from_dt) as min_from_dt from jklee.test_dates group by key_cd ) as src_data set to_dt = src_data.max_from_dt where test_dates.key_cd = src_data.key_cd and test_dates.from_dt = src_data.min_from_dt ;
Runing this on the above data results in:
select * from jklee.test_dates order by key_cd, from_dt ; key_cd from_dt to_dt A 1 2 A 2 99 B 1 2 B 2 99 C 2 2
If I am right in my assumption on what you are tyring to do (eliminate overlaps and gaps in time variant data), then you need some more conditions on the query. You probably wouldn't want the from date to be greater than the to date, though depending on the current data, that could happen. Adding a condition to avoid that would rpbably mean a correlated query, which is something you probably want to avoid.
I had a quick glance over the resulted answer set and found that the query has not meet the requirement for the key_cd = 2.
key_cd from_dt to_dt A 1 2 A 2 99 B 1 2 B 2 99 C 2 2
The to_dt for the key_cd = 2 must be 99 as same as before the update process. But that has been distrubed.
Please find the resolution to overcome this issue and let me know if this works for you.
Update A from jklee.test_dates a ,( select b.key_cd as key1 ,max(b.from_dt) as dt1 from jklee.test_dates b group by 1 ) as c set to_dt = c.dt1 where a.key_cd = c.key1 and a.from_dt = (select min(d.from_dt) from jklee.test_dates d where d.key_cd = a.key_cd) and a.from_dt <> c.dt1 ;
key_cd from_dt to_dt A 1 2 A 2 99 B 1 2 B 2 99 C 2 99