PL/SQL update question

When I run the code below it errors with "ORA-01427: single-row subquery returns more than one row" for the select ftvacct_atyp_code.
UPDATE ftvacct x
   SET ftvacct_atyp_code =
                       (SELECT ftvacct_atyp_code
                          FROM ftvacct y
                         WHERE y.ftvacct_acct_code = x.ftvacct_acct_code_pred)
 WHERE ftvacct_acct_code_pred IS NOT NULL;

oh.  I assumed ftvacct_acct_code was a unique identifier for ftvacct

what are the rest of the columns in the table?  what makes a row unique?

1100                                                                             AK
11110                          11010                                       AF
11010                          1100                                         AB

sdstuber,  given the above data, won't your result look like:

1100                                                                             AK
11110                          11010                                       AB
11010                          1100                                         AK

instead of what I assume is the desired result is
1100                                                                             AK
11110                          11010                                       AK
11010                          1100                                         AK

yes it would, I don't know if a recursive update is what is expected or not

What makes it unique is the following: FTVACCT_COAS_CODE, FTVACCT_ACCT_CODE, FTVACCT_EFF_DATE, FTVACCT_NCHG_DATE

Here's what the table looks like
CREATE TABLE FTVACCT
(
  FTVACCT_COAS_CODE             VARCHAR2(1 BYTE) NOT NULL,
  FTVACCT_ACCT_CODE             VARCHAR2(6 BYTE) NOT NULL,
  FTVACCT_EFF_DATE              DATE            NOT NULL,
  FTVACCT_ACTIVITY_DATE         DATE            NOT NULL,
  FTVACCT_USER_ID               VARCHAR2(30 BYTE) NOT NULL,
  FTVACCT_NCHG_DATE             DATE            NOT NULL,
  FTVACCT_TERM_DATE             DATE,
  FTVACCT_TITLE                 VARCHAR2(35 BYTE) NOT NULL,
  FTVACCT_ATYP_CODE             VARCHAR2(2 BYTE) NOT NULL,
  FTVACCT_NORMAL_BAL            VARCHAR2(1 BYTE) NOT NULL,
  FTVACCT_STATUS_IND            VARCHAR2(1 BYTE) NOT NULL,
  FTVACCT_ACCT_CODE_PRED        VARCHAR2(6 BYTE),
  FTVACCT_DATA_ENTRY_IND        VARCHAR2(1 BYTE) NOT NULL,
  FTVACCT_ACCT_CODE_FRINGE      VARCHAR2(6 BYTE),
  FTVACCT_FRINGE_PCT            NUMBER(6,3),
  FTVACCT_ACCT_CODE_POOL        VARCHAR2(6 BYTE),
  FTVACCT_ACCT_CODE_ASSET       VARCHAR2(6 BYTE),
  FTVACCT_ITYP_SEQ_CODE         NUMBER(2),
  FTVACCT_ACCT_CLASS_CODE       VARCHAR2(2 BYTE),
  FTVACCT_ACCT_CODE_DEPR_EXP    VARCHAR2(6 BYTE),
  FTVACCT_ACCT_CODE_DEPR_ACCUM  VARCHAR2(6 BYTE)
)

wjseaman,
does the it need to be recursive? Please see my previous post and sdstuber's reply.

neither my question or your table description answer the intent of the update though.

what wjseaman is trying to suggest is

given this data....

1100                                                                             AK
11110                          11010                                       AF
11010                          1100                                         AB

11010 points to 1100 as predecssor so it updates the AB to be AK

11110 points to 11010 points to 11010 so it updates the AF to AB.


but,  11010 is going to be updated to be AK, so, should 11110 inherit that change too?
or should it only be updated to the value 11010 had at the start of the update.

If each row should follow the predecesor back to the root value then the update needs to recursive.

Right now my query does not do the recursion, so there isn't any inheritance from a chain of predecessors.


What is the intent?

In this example:  11010 and 11110 should  inherit the value AK from 1100.  11010 is easy to see since it's ftvacct_acct_code_pred code is 1100.  However, 11110, you have to follow it thru 11010 to 1100 to see the correct value  of AK.  

can you provide some rows of complete data?

In copying the data, I've notice one thing, there are two things that determine the root value record, not only is ftvacct_acct_code_pred = NULL, but the FTVACCT_NCHG_DATE = 31-DECEMBER-2099.  Sorry for any confusion, I simply overlooked it.

Any one have any ideas?

given the above data in that spreadsheet, what do you want the results to be?

For example
FTVACCT_Acct_Code FTVACCT_Acct_Code_pred FTVACCT_atyp_code
---------------------------- ------------------------------------- ---------------------------
1000                                                                             AA
10010                          1000                                         AA
1050                                                                             AF
10510                           1050                                        AF
1100                                                                             AK
11010                          1100                                         AK
11110                          11010                                       UA
****************************************************************************************
The record 11110 atyp_code is incorrect.  It should be AK because it's code_pred is 11010 and that one's atyp_code is  AK and it's code_pred is 1100 and 1100's code_pred is NULL.  The one with the Null is the driving code.  It should flow thru all of the code_pred.

you're posting what you don't want to see then trying to back into the correct results.  I'm sorry that just confuses me.

please post exactly the results you want to see, for the sample input data you have posted.

Here is the file like you wanted.  I've removed the columns that have no impact.

that appears to be your input file again, except abbreviated.

what is the expected output of that file?