New to Teradata - Update a table with another records in same table

Database
Enthusiast

New to Teradata - Update a table with another records in same table

Hi Experts,

I am new to teradata and a lot of posts on these forms have helped me a great deal but I cannot find the answer to a problem I have. Hoping someone can help me out here.

I have a table with the following layout:

State | Account_ID | Member_ID | Duration | Product_To | Product_From

IL | 001 | 111 | 1 | Prod 1 | NULL

IL | 001 | 111 | 2 | Prod 1 | NULL

IL | 001 | 111 | 3 | Prod 2 | NULL

IL | 001 | 111 | 4 | Prod 3 | NULL

IL | 001 | 111 | 5 | Prod 6 | NULL

IL | 001 | 111 | 6 | Prod 7 | NULL

I need to populate the Product_From field on each row with the one before it.  I need to use the duration column to find the previous record.  So the table should end up looking like this:

State | Account_ID | Member_ID | Duration | Product_To | Product_From

IL | 001 | 111 | 1 | Prod 1 | NULL

IL | 001 | 111 | 2 | Prod 1 | Prod 1

IL | 001 | 111 | 3 | Prod 2 | Prod 1

IL | 001 | 111 | 4 | Prod 3 | Prod 2

IL | 001 | 111 | 5 | Prod 6 | Prod 3

IL | 001 | 111 | 6 | Prod 7 | Prod 6

I tried the following update statements but no luck:

UPDATE SCHEMA.TABLE

   SET PRODUCT_FROM = (SELECT B.PRODUCT

                               FROM SCHEMA.TABLE AS B

                              WHERE B.STATE = SCHEMA.TABLE.STATE

                                AND B.ACCOUNT_ID = SCHEMA.TABLE.ACCOUNT_ID

                                AND B.MEMBER_ID = SCHEMA.TABLE.MEMBER_ID

                                AND B.MBR_DURATION = SCHEMA.TABLE.MBR_DURATION - 1);

UPDATE SCHEMA.TABLE

  FROM (SELECT A.STATE

             , A.ACCOUNT_ID

             , A.MEMBER_ID

             , A.PRODUCT_FROM

             , B.PRODUCT

          FROM SCHEMA.TABLE AS B

             , SCHEMA.TABLE AS A

         WHERE B.STATE = A.STATE

           AND B.ACCOUNT_ID = A.ACCOUNT_ID

           AND B.MEMBER_ID = A.MEMBER_ID

           AND B.MBR_DURATION -1 = A.MBR_DURATION) C

   SET PRODUCT_FROM = C.PRODUCT

 WHERE SCHEMA.TABLE.STATE = C.STATE

   AND SCHEMA.TABLE.ACCOUNT_ID = C.ACCOUNT_ID

   AND SCHEMA.TABLE.MEMBER_ID = C.MEMBER_ID;

Please help if you can.  I really appreciate any assistance.  If an update statement is not the best solution, I can use macros or stored procs also but I am not sure where to begin.

Thanks!!

Tags (1)
10 REPLIES
Enthusiast

Re: New to Teradata - Update a table with another records in same table

Try this SQL

UPDATE T1
FROM SCHEMA.TABLE T1, SCHEMA.TABLE T2
SET PRODUCT_FROM = T2.PRODUCT_TO
WHERE T1.DURATION = T2.DURATION + 1

Good luck!

Enthusiast

Re: New to Teradata - Update a table with another records in same table

That seems simple enough, QAKiani..I got this error though:

UPDATE Failed. 7547:  Target row updated by multiple source rows.

Enthusiast

Re: New to Teradata - Update a table with another records in same table

It seems like there are duplicate values in the DURATION column. From the sample data I assumed that the DURATION column has unique values.

You can include the other columns in the WHERE clause which uniquely identifies all the rows (may be you can add STATE, ACCOUNT_ID, MEMBER_ID...)

Enthusiast

Re: New to Teradata - Update a table with another records in same table

Yup..that's what I ran..here's the SQL:

UPDATE T1

FROM SCHEMA.TABLE T1, SCHEMA.TABLE T2

SET PROD_TYP_CD_FROM = T2.PROD_TYP_CD

WHERE T1.MBR_DURATION = T2.MBR_DURATION + 1

  AND T1.CORP_ENT_CD = T2.CORP_ENT_CD

  AND T1.DW_ACCT_KEY = T2.DW_ACCT_KEY

  AND T1.MID_PTY_ID = T2.MID_PTY_ID

Thanks!

Enthusiast

Re: New to Teradata - Update a table with another records in same table

so the problem fixed?

Enthusiast

Re: New to Teradata - Update a table with another records in same table

Oh..sorry I was not clear at first..I ran the query with the extra feilds in join criteria and got that error..

Query ran:

UPDATE T1

FROM SCHEMA.TABLE T1, SCHEMA.TABLE T2

SET PROD_TYP_CD_FROM = T2.PROD_TYP_CD

WHERE T1.MBR_DURATION = T2.MBR_DURATION + 1

  AND T1.CORP_ENT_CD = T2.CORP_ENT_CD

  AND T1.DW_ACCT_KEY = T2.DW_ACCT_KEY

  AND T1.MID_PTY_ID = T2.MID_PTY_ID

Error:

UPDATE Failed. 7547:  Target row updated by multiple source rows.

Enthusiast

Re: New to Teradata - Update a table with another records in same table

UPDATE T1

FROM

(

SEL DISTINCT DURATION ,...

FROM TABLE T2

WHERE 

) T2

SET PROD_TYP_CD_FROM = T2.PROD_TYP_CD

WHERE T1.MBR_DURATION = T2.MBR_DURATION + 1

  AND T1.CORP_ENT_CD = T2.CORP_ENT_CD

  AND T1.DW_ACCT_KEY = T2.DW_ACCT_KEY

  AND T1.MID_PTY_ID = T2.MID_PTY_ID

Enthusiast

Re: New to Teradata - Update a table with another records in same table

You need to check whether the STATE, ACCOUNT_ID, MEMBER_ID, DURATION combination is unique or not across the table.

SEL STATE, ACCOUNT_ID, MEMBER_ID, DURATION, COUNT(*) AS CNT
FROM SCHEMA_TABLE
GROUP BY 1,2,3,4
HAVING CNT > 1

From the error it seems like there are multiple products (could be both unique or duplicate) in the PRODUCT_TO column for the same duration for one account under one membership in one state. Is this a valid scenario?

If yes, then the columns in the where clause are not enough and you need to identify another column which alongwith other ones can uniquely identify the row.

Enthusiast

Re: New to Teradata - Update a table with another records in same table

Experts, you were correct - there were duplicated records.  I removed them and this SQL worked!

UPDATE T1

FROM SCHEMA.TABLE T1, SCHEMA.TABLE T2

SET PROD_TYP_CD_FROM = T2.PROD_TYP_CD

WHERE T1.MBR_DURATION = T2.MBR_DURATION + 1

  AND T1.CORP_ENT_CD = T2.CORP_ENT_CD

  AND T1.DW_ACCT_KEY = T2.DW_ACCT_KEY

  AND T1.MID_PTY_ID = T2.MID_PTY_ID

Thanks for your help!!