All Forums Database
mabsiddiqui 5 posts Joined 07/12
16 Jul 2012
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:
Qaisar Kiani 337 posts Joined 11/05
16 Jul 2012

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!

mabsiddiqui 5 posts Joined 07/12
16 Jul 2012

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

 

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

Qaisar Kiani 337 posts Joined 11/05
16 Jul 2012

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...)

 

mabsiddiqui 5 posts Joined 07/12
17 Jul 2012

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!

Qaisar Kiani 337 posts Joined 11/05
17 Jul 2012

so the problem fixed?

mabsiddiqui 5 posts Joined 07/12
17 Jul 2012

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.

 

 

Jigar 70 posts Joined 09/11
17 Jul 2012

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

Qaisar Kiani 337 posts Joined 11/05
17 Jul 2012

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.

mabsiddiqui 5 posts Joined 07/12
19 Jul 2012

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!!

satrajitchandra 1 post Joined 05/13
22 May 2013

Solution:

 

update ggg_1 t1

   set t1.product_from = (select product_to

                            from ggg_1 t2

                           where t2.duration = t1.duration - 1)

 where t1.duration != 1;

 

 

 

 

 

You must sign in to leave a comment.