I have a bunch of tables that I need to link together with the goal of updating the name and address in the ub_gl_movement Table (relevant columns listed).
|ub_gl_movement|
:-------------------------------------------------------------------------------------------------:
|UBGL_UBCT_CONTRACT_ID|UBGL_UBIN_INVESTMENT_ID|UBGL_PAYEE_NAME|ADDR_1 |ADDR_2|ADDR_3|SUBURB |
:-------------------------------------------------------------------------------------------------:
|194875 |000000Z0000480 |John Smith |123 Some St|(null)|(null)|Somewhere|
The zz_person table is as follows
|zz_person|
:-------------------------------------------:
|ZZPD_PERSON_ID|ZZPD_FIRST_NAME|ZZPD_SURNAME|
:-------------------------------------------:
|21916908 |Bill |Jones |
The Address table is as follows
|ADDRESS|
:-----------------------------------------------------------:
|ZZAD_ZZPD_PERSON_ID|ZZAD_LINE_1 |ZZAD_LINE_2|ZZAD_LINE_3|
:-----------------------------------------------------------:
|21916908 |456 Main Street|(null) |(null) |
There's no way to link these tables as is, so I tried to link via another table called zz_investment_person using the INVESTMENT_ID and PERSON_ID.
EDIT: Adding DESC UB_INVESTMENT
DESC UTB.UB_INVESTMENT
Name Null Type
-------------------------- -------- ------------
UBIN_INVESTMENT_ID NOT NULL VARCHAR2(14)
UBIN_DATE_FROM NOT NULL DATE
UBIN_DATE_TO NOT NULL DATE
UBIN_USER_LAST_UPD NOT NULL VARCHAR2(8)
UBIN_DATE_LAST_UPD NOT NULL DATE
UBIN_UBTC_COUNTRY_CODE VARCHAR2(8)
UBIN_REF_TAX_INVEST_TYPE VARCHAR2(8)
UBIN_BAD_TAX_EXEMPTION_YN VARCHAR2(1)
UBIN_WITH_TAX_EXEMPTION_YN VARCHAR2(1)
UBIN_TFN_TAX_EXEMPTION_YN VARCHAR2(1)
UBIN_EXTERNAL_REF VARCHAR2(40)
UBIN_REF_EXTRACT_MAIL VARCHAR2(8)
UBIN_VESTING_EXIST_YN VARCHAR2(1)
UBIN_REF_TAX_LEVEL VARCHAR2(8)
UBIN_UBML_MARGIN_LENDER_ID VARCHAR2(10)
UBIN_REF_BUSINESS_TYPE VARCHAR2(8)
UBIN_REF_BUSINESS_DIVISION VARCHAR2(8)
UBIN_REF_SOURCE VARCHAR2(8)
UBIN_CUSTOMER_REF_NUMBER VARCHAR2(18)
|zz_investment_person|
:--------------------------------------:
|ZZIP_INVESTMENT_ID|ZZIP_ZZPD_PERSON_ID|
:--------------------------------------:
|000000Z0000480 |21916908 |
DESC UTB.ZZ_INVESTMENT_PERSON
Name Null Type
------------------------ -------- ------------
ZZIP_INVESTMENT_ID NOT NULL VARCHAR2(14)
ZZIP_ZZPD_PERSON_ID NOT NULL NUMBER(8)
ZZIP_REF_PERSON_RELN NOT NULL VARCHAR2(8)
ZZIP_DATE_FROM NOT NULL DATE
ZZIP_DATE_TO NOT NULL DATE
ZZIP_USER_LAST_UPD NOT NULL VARCHAR2(8)
ZZIP_DATE_LAST_UPD NOT NULL DATE
ZZIP_VEST_AGE NUMBER(2)
ZZIP_DATE_VEST_EFFECTIVE DATE
ZZIP_DATE_VEST_PROCESSED DATE
I need to update the ub_gl_movement table so that the UBGL_PAYEE_NAME is set to Bill Jones, with an address of 456 Main Street. My query is
UPDATE
(
SELECT G.UBGL_PAYEE_NAME PAYEE_NAME,
P.ZZPD_FIRST_NAME F_NAME,
P.ZZPD_SURNAME L_NAME
FROM UTB.UB_GL_MOVEMENT G
JOIN UTB.UB_INVESTMENT I
ON G.UBGL_UBIN_INVESTMENT_ID = I.UBIN_INVESTMENT_ID
JOIN UTB.ZZ_INVESTMENT_PERSON IP
ON IP.ZZIP_INVESTMENT_ID = I.UBIN_INVESTMENT_ID
JOIN UTB.ZZ_PERSON P
ON P.ZZPD_PERSON_ID = IP.ZZIP_ZZPD_PERSON_ID
WHERE G.UBGL_PAYEE_NAME IS NOT NULL
)
SET PAYEE_NAME = F_NAME || ' ' || L_NAME;
However I get the error
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
and if I run this query
UPDATE UTB.UB_GL_MOVEMENT G
SET G.UBGL_PAYEE_NAME =
(SELECT CASE WHEN (UPPER(ZZPD_REF_TITLE) = 'EST OF')
THEN SUBSTR(ZZPD_REF_TITLE, 1, 3) || ' ' || ZZPD_FIRST_NAME || ' ' || ZZPD_SURNAME
WHEN (ZZPD_REF_TITLE IS NOT NULL AND ZZPD_FIRST_NAME IS NOT NULL AND ZZPD_SURNAME IS NOT NULL )
THEN ZZPD_REF_TITLE || ' ' || ZZPD_FIRST_NAME || ' ' || ZZPD_SURNAME
WHEN (ZZPD_REF_TITLE IS NULL AND ZZPD_FIRST_NAME IS NOT NULL AND ZZPD_SURNAME IS NOT NULL)
THEN ZZPD_FIRST_NAME || ' ' || ZZPD_SURNAME
ELSE 'SIR / MADAM' END
FROM UTB.ZZ_PERSON P
JOIN UTB.ZZ_INVESTMENT_PERSON IP
ON P.ZZPD_PERSON_ID = IP.ZZIP_ZZPD_PERSON_ID
JOIN UTB.UB_INVESTMENT I
ON G.UBGL_UBIN_INVESTMENT_ID = I.UBIN_INVESTMENT_ID
AND ROWNUM = 1
)
WHERE G.UBGL_PAYEE_NAME IS NOT NULL;
I get a different error
Error at Command Line:14 Column:21
Error report:
SQL Error: ORA-00904: "G"."UBGL_UBIN_INVESTMENT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
I haven't updated the Address table in this query. I figured I'll just start with the names first.
Any input would be greatly appreciated :)
EDIT 2 - I have changed the second update query to
UPDATE UTB.UB_GL_MOVEMENT G
SET G.UBGL_PAYEE_NAME = (SELECT CASE WHEN (UPPER(ZZPD_REF_TITLE) = 'EST OF')
THEN SUBSTR(ZZPD_REF_TITLE, 1, 3) || ' ' || ZZPD_FIRST_NAME || ' ' || ZZPD_SURNAME
WHEN (ZZPD_REF_TITLE IS NOT NULL AND ZZPD_FIRST_NAME IS NOT NULL AND ZZPD_SURNAME IS NOT NULL )
THEN ZZPD_REF_TITLE || ' ' || ZZPD_FIRST_NAME || ' ' || ZZPD_SURNAME
WHEN (ZZPD_REF_TITLE IS NULL AND ZZPD_FIRST_NAME IS NOT NULL AND ZZPD_SURNAME IS NOT NULL)
THEN ZZPD_FIRST_NAME || ' ' || ZZPD_SURNAME
ELSE 'SIR / MADAM' END
FROM UTB.ZZ_PERSON P
JOIN UTB.ZZ_INVESTMENT_PERSON IP ON P.ZZPD_PERSON_ID = IP.ZZIP_ZZPD_PERSON_ID
JOIN UTB.UB_INVESTMENT I ON I.UBIN_INVESTMENT_ID = IP.ZZIP_INVESTMENT_ID
JOIN UTB.UB_GL_MOVEMENT GMT ON GMT.UBGL_UBIN_INVESTMENT_ID = I.UBIN_INVESTMENT_ID
WHERE ROWNUM = 1
)
WHERE G.UBGL_PAYEE_NAME IS NOT NULL;
which does execute, but all PAYEE_NAMEs are the same, so it seems it has taken the first row from the ZZ_PERSON table and used that for all rows, instead of searching the table for the relevant ID / Name combination. If I remove the rownum=1, I get an error
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
