Updating "EndDate" using subselects

Database

Updating "EndDate" using subselects

Hi,

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)

Can you help?
4 REPLIES
Enthusiast

Re: Updating "EndDate" using subselects

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.

Re: Updating "EndDate" using subselects

Excellent! This is exactly what I needed. Thanks for your help.
Enthusiast

Re: Updating "EndDate" using subselects

glad to help.

Re: Updating "EndDate" using subselects

Hi jklee/Groupie,

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

Regards,
Raghav.