SQL - Update stmt

[Uma]

CREATE TABLE POL_HI_NBR (
POLICY_ID NUMERIC(15,0) NOT NULL,
UNITCOMMON_ID NUMERIC(15,0) NOT NULL,
HI_NBR_UNIQUE_C ODE CHAR(10) NOT NULL,
HI_NBR_I INTEGER NOT NULL
);

CREATE TABLE POL_UNITCOMMON (
UNITCOMMON_ID NUMERIC(15,0) NOT NULL,
POLICY_ID NUMERIC(15,0) NOT NULL,
LOGICAL_ENTITY_ ID INTEGER NOT NULL,
VERS_ORIG_MAJ_M IN INTEGER NOT NULL,
ROW_ACTIVE_STS_ D INTEGER NOT NULL,
COMPANY_ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
PROGRAM_ID INTEGER NOT NULL,
PRIMARY_PRODUCT _F CHAR(1) NOT NULL,
UNIT_TYPE_C CHAR(10) NOT NULL,
UNIT_NBR_I INTEGER NOT NULL,
UNIT_TYPE_PROFI LE CHAR(10),
NOTE_ATTACHED_F CHAR(1) NOT NULL,
ORIG_EFF_D INTEGER NOT NULL,
PARENT_UNITCOMM ON NUMERIC(15,0),
PRIOR_UNTCOMMON _ID NUMERIC(15,0),
AMEND_NBR_1_2_I INTEGER NOT NULL
);


hI I got above 2 tables.Right now POL_HI_NBR.UNIT COMMON_ID containS
THE datafrom POL_UNITCOMMON. UNICOMMON_ID. Based upon some
POL_HI_NBR.HI_N BR_UNIQUE_CODE Condation , I have to update
POL_HI_NBR.UNIT COMMON_ID with POL_UNITCOMMON_ PRODUCT_ID ??

i TRIED MANY WAYS .. no luck ..

UPDATE POL_HI_NBR Z
SET Z.UNITCOMMON_ID =
(SELECT A.PRODUCT_ID
FROM POL_UNITCOMMON AS A,
POL_HI_NBR AS B
WHERE A.POLICY_ID=B.P OLICY_ID
AND A.UNITCOMMON_ID =B.UNITCOMMON_I D
AND B.POLICY_ID=Z.P OLICY_ID
AND B.UNITCOMMON_ID =Z.UNITCOMMON_I D
AND B.HI_NBR_UNIQUE _CODE='CPU')
WHERE Z.UNITCOMMON_ID IN
(SELECT D.UNITCOMMON_ID
FROM POL_UNITCOMMON AS D,
POL_HI_NBR AS E
WHERE D.POLICY_ID=E.P OLICY_ID
AND D.UNITCOMMON_ID =E.UNITCOMMON_I D
AND E.POLICY_ID=Z.P OLICY_ID
AND E.UNITCOMMON_ID =Z.UNITCOMMON_I D
AND E.HI_NBR_UNIQUE _CODE='CPU')

any ideas..

Thanks
Uma

[Christian Maslen]

Re: SQL - Update stmt

Hi Uma,
[color=blue]
> i TRIED MANY WAYS .. no luck ..
>
> UPDATE POL_HI_NBR Z
> SET Z.UNITCOMMON_ID =
> (SELECT A.PRODUCT_ID
> FROM POL_UNITCOMMON AS A,
> POL_HI_NBR AS B
> WHERE A.POLICY_ID=B.P OLICY_ID
> AND A.UNITCOMMON_ID =B.UNITCOMMON_I D
> AND B.POLICY_ID=Z.P OLICY_ID
> AND B.UNITCOMMON_ID =Z.UNITCOMMON_I D
> AND B.HI_NBR_UNIQUE _CODE='CPU')
> WHERE Z.UNITCOMMON_ID IN
> (SELECT D.UNITCOMMON_ID
> FROM POL_UNITCOMMON AS D,
> POL_HI_NBR AS E
> WHERE D.POLICY_ID=E.P OLICY_ID
> AND D.UNITCOMMON_ID =E.UNITCOMMON_I D
> AND E.POLICY_ID=Z.P OLICY_ID
> AND E.UNITCOMMON_ID =Z.UNITCOMMON_I D
> AND E.HI_NBR_UNIQUE _CODE='CPU')
>
> any ideas..[/color]


Try the following:

UPDATE POL_HI_NBR Z
SET Z.UNITCOMMON_ID = (SELECT A.PRODUCT_ID
FROM POL_UNITCOMMON AS A
WHERE A.POLICY_ID= Z.POLICY_ID
AND A.UNITCOMMON_ID = Z.UNITCOMMON_ID )
WHERE Z.HI_NBR_UNIQUE _CODE='CPU'
AND Z.UNITCOMMON_ID IN (SELECT D.UNITCOMMON_ID
FROM POL_UNITCOMMON AS D
WHERE D.POLICY_ID= Z.POLICY_ID);

or

UPDATE POL_HI_NBR Z
SET Z.UNITCOMMON_ID = (SELECT A.PRODUCT_ID
FROM POL_UNITCOMMON AS A
WHERE A.POLICY_ID= Z.POLICY_ID
AND A.UNITCOMMON_ID = Z.UNITCOMMON_ID )
WHERE Z.HI_NBR_UNIQUE _CODE='CPU'
AND EXISTS (SELECT *
FROM POL_UNITCOMMON AS D
WHERE D.POLICY_ID= Z.POLICY_ID
AND D.UNITCOMMON_ID = Z.UNITCOMMON_ID );


The idea is you need to join A to Z. Not A to a different version of
POL_HI_NBR (B above). This query will only work if there is a one to
one relationship between POL_UNITCOMMON and POL_HI_NBR (on policy_id
and unitcommon_id).

Christian.