how to update rows by a range of dates in teradata

Question

Hi,

I have two tables in a teradata database that look like this

accounts
account_number integer
date_updated_last datetime
delinquency_code varchar(3)

payments
account_number integer
statement_date datetime
delinquency_code varchar(3)

the delinquency code column is populated in accounts, but is not populated in payments. I would like to update payments with the delinquency code based on date_updated_last and statement_date. the problem is that statement_date is sequential, say a given account was opened in july of 2009, there would be one record for every month between then and now, but an account record is only added when the information changes, so there may be, for example, only 3 records in the account table for the same account. Say, august 2009, january 2010, and march 2010. so I would want to update all the payment records between august 2009 and january 2010 with the data from the august 2009 record in accounts. can anyone point me to an easy way to do this?

thank you :)

-C

Answer 1

Ok, here's another try at the Teradata syntax:

UPDATE
    Payments
FROM
    (
        SELECT
            A1.account_number,
            A1.date_updated_last AS begin_date,
            A2.date_updated_last AS end_date,
            A1.delinquency_code
        FROM
            Accounts A1
        INNER JOIN Accounts A2 ON
            A2.account_number = A1.account_number AND
            A2.date_updated_last > A1.date_updated_last
        WHERE
            NOT EXISTS
            (
                SELECT *
                FROM
                    Accounts A3
                WHERE
                    A3.account_number = A1.account_number AND
                    A3.date_updated_last > A1.date_updated_last AND
                    A3.date_updated_last < A2.date_updated_last
            )
    ) AS SQ (account_number, begin_date, end_date, delinquency_code)
SET
    delinquency_code = SQ.delinquency_code
WHERE
    account_number = SQ.account_number AND
    statement_date >= SQ.begin_date AND
    statement_date < SQ.end_date