Error 7547 on Update

Database
Enthusiast

Error 7547 on Update

Hello,

I'm running an update, and as I develop it, from updating everything incorrectly, to error 7547's, to spool space, and collecting stats on tables invloved, distinct, with and without qualify row_num, I get 7547 or a spool stll. Can someone please help me with this update statement.

There are multirows in the inner, and I only need to know if anything like '%abc%' is present in that condition to them mark a set column what has a 'n' to become a 'y', and I either spool, or keep pulling back more than one row that it wants.

TD Version - 15.10.01.04

Thank you

UPDATE DA

FROM DATABASENAME.DIG_ADOPT AS DA ,

    (SELECT

    DISTINCT(CG.ACCT_NBR)

    FROM DATABASENAME.SRT_CG AS CG

        , DATABASENAME.DIG_ADOPT AS DA

    WHERE CG.ACCT_NBR = DA.ACCT_NBR AND CG.OFFERED LIKE '%ABC%'

    QUALIFY ROW_NUMBER() OVER(PARTITION BY DATABASENAME.SRT_CG.ACCT_NBR ORDER BY CG.OFFERED DESC) = 1

    --GROUP BY 1) AS SRC

    ) AS SRC

SET OFFR_ABCO = 'Y'

WHERE SRC.ACCT_NBR = DATABASENAME.DIG_ADOPT.ACCT_NBR ;

Tags (1)
5 REPLIES
Senior Apprentice

Re: Error 7547 on Update

Can you show Explain?

Double check your table aliases, seems like you get a product-join.

Enthusiast

Re: Error 7547 on Update

Here it is with datbase/table names changes to be like the example

UPDATE DA

FROM DATABASENAME.DIG_ADOPT AS DA ,

    (SELECT

    DISTINCT(CG.ACCT_NBR)

    FROM DATABASENAME.SRT_CG AS CG

        , DATABASENAME.DIG_ADOPT AS DA

    WHERE CG.ACCT_NBR = DA.ACCT_NBR AND CG.OFFERED LIKE '%SSO%'

    QUALIFY ROW_NUMBER() OVER(PARTITION BY DATABASENAME.SRT_CG.ACCT_NBR ORDER BY cg.OFFERED DESC) = 1

    --GROUP BY 1) AS SRC

    ) AS SRC

SET OFFR_SSO = 'Y'

WHERE SRC.ACCT_NBR = DA.ACCT_NBR;

  1) First, we lock DATABASENAME.DIG_ADOPT for write on

     a reserved RowHash to prevent global deadlock.

  2) Next, we lock DATABASENAME.CG for read on a reserved RowHash to

     prevent global deadlock.

  3) We lock DATABASENAME.DIG_ADOPT for write, and we

     lock DATABASENAME.CG for read.

  4) We do an all-AMPs RETRIEVE step from DATABASENAME.CG by way of an

     all-rows scan with a condition of ("(NOT (DATABASENAME.CG.ACCT_NBR IS

     NULL )) AND (DATABASENAME.CG.OFFERED LIKE '%SSO%')") into Spool 3

     (all_amps), which is redistributed by the hash code of (

     DATABASENAME.CG.ACCT_NBR) to all AMPs.  Then we do a SORT to order

     Spool 3 by row hash.  The size of Spool 3 is estimated with low

     confidence to be 1,856 rows (647,744 bytes).  The estimated time

     for this step is 0.00 seconds.

  5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a

     RowHash match scan, which is joined to DATABASENAME.DA by way of a

     RowHash match scan with no residual conditions.  Spool 3 and

     DATABASENAME.DA are joined using a merge join, with a join condition

     of ("ACCT_NBR = DATABASENAME.DA.Acct_Nbr").  The result goes into

     Spool 4 (all_amps), which is duplicated on all AMPs.  The size of

     Spool 4 is estimated with index join confidence to be 2,886,408

     rows (1,007,356,392 bytes).  The estimated time for this step is

     0.25 seconds.

  6) We do an all-AMPs JOIN step from DATABASENAME.SRT_CG by way

     of an all-rows scan with no residual conditions, which is joined

     to Spool 4 (Last Use) by way of an all-rows scan.

     DATABASENAME.SRT_CG and Spool 4 are joined using a product

     join, with a join condition of ("(1=1)").  The result goes into

     Spool 2 (all_amps), which is built locally on the AMPs.  The size

     of Spool 2 is estimated with index join confidence to be

     18,610,841 rows (9,659,026,479 bytes).  The estimated time for

     this step is 0.24 seconds.

  7) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by

     way of an all-rows scan into Spool 7 (Last Use), which is assumed

     to be redistributed by value to all AMPs.  The result rows are put

     into Spool 1 (all_amps), which is redistributed by hash code to

     all AMPs.  Then we do a SORT to order Spool 1 by the sort key in

     spool field1 eliminating duplicate rows.  The size is estimated

     with index join confidence to be 9,749 rows (6,658,567 bytes).

  8) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

     an all-rows scan with a condition of ("NOT (SRC.ACCT_NBR IS NULL)")

     into Spool 11 (all_amps), which is redistributed by the hash code

     of (DATABASENAME.CG.ACCT_NBR) to all AMPs.  Then we do a SORT to order

     Spool 11 by row hash.  The size of Spool 11 is estimated with

     index join confidence to be 9,749 rows (1,764,569 bytes).  The

     estimated time for this step is 0.01 seconds.

  9) We do an all-AMPs JOIN step from

     DATABASENAME.DIG_ADOPT by way of a RowHash match

     scan with no residual conditions, which is joined to Spool 11

     (Last Use) by way of a RowHash match scan.

     DATABASENAME.DIG_ADOPT and Spool 11 are joined

     using a merge join, with a join condition of ("ACCT_NBR =

     DATABASENAME.DIG_ADOPT.Acct_Nbr").  The result goes

     into Spool 10 (all_amps), which is built locally on the AMPs.

     Then we do a SORT to order Spool 10 by the sort key in spool

     field1 (DATABASENAME.DIG_ADOPT.ROWID).  The size of

     Spool 10 is estimated with index join confidence to be 10,025 rows

     (180,450 bytes).  The estimated time for this step is 0.01 seconds.

 10) We do a MERGE Update to DATABASENAME.DIG_ADOPT from

     Spool 10 (Last Use) via ROWID.  The size is estimated with index

     join confidence to be 10,025 rows (1,493,725 bytes).  The

     estimated time for this step is 0.08 seconds.

 11) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

Enthusiast

Re: Error 7547 on Update

I see that product join,and I have made many joins after the set statement and still cant get past the spool

Senior Apprentice

Re: Error 7547 on Update

You must use a defined alias instead of the original table name:

UPDATE DA
FROM DATABASENAME.DIG_ADOPT AS DA ,
(SELECT
DISTINCT(CG.ACCT_NBR)
FROM DATABASENAME.SRT_CG AS CG
, DATABASENAME.DIG_ADOPT AS DA
WHERE CG.ACCT_NBR = DA.ACCT_NBR AND CG.OFFERED LIKE '%ABC%'
) AS SRC
SET OFFR_ABCO = 'Y'
WHERE SRC.ACCT_NBR = DA.ACCT_NBR ;

But as you only want to set a flag you better use EXISTS:

UPDATE DATABASENAME.DIG_ADOPT
SET OFFR_ABCO = 'Y'
WHERE EXISTS
( SELECT
FROM DATABASENAME.SRT_CG AS CG
WHERE CG.ACCT_NBR = DATABASENAME.DIG_ADOPT.ACCT_NBR AND CG.OFFERED LIKE '%ABC%'
);
Enthusiast

Re: Error 7547 on Update

They both work, I see what you're saying about using defined alias and not original table name, I like the EXISTS as it's cleaner. Thank you very much Dieter