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_CODE 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_MIN 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_PROFILE CHAR(10),
NOTE_ATTACHED_F CHAR(1) NOT NULL,
ORIG_EFF_D INTEGER NOT NULL,
PARENT_UNITCOMMON 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.UNITCOMMON_ID containS
THE datafrom POL_UNITCOMMON.UNICOMMON_ID. Based upon some
POL_HI_NBR.HI_NBR_UNIQUE_CODE Condation , I have to update
POL_HI_NBR.UNITCOMMON_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.POLICY_ID
AND A.UNITCOMMON_ID=B.UNITCOMMON_ID
AND B.POLICY_ID=Z.POLICY_ID
AND B.UNITCOMMON_ID=Z.UNITCOMMON_ID
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.POLICY_ID
AND D.UNITCOMMON_ID=E.UNITCOMMON_ID
AND E.POLICY_ID=Z.POLICY_ID
AND E.UNITCOMMON_ID=Z.UNITCOMMON_ID
AND E.HI_NBR_UNIQUE_CODE='CPU')

any ideas..

Thanks
Uma

[Christian Maslen]

Hi Uma,

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.POLICY_ID
AND A.UNITCOMMON_ID=B.UNITCOMMON_ID
AND B.POLICY_ID=Z.POLICY_ID
AND B.UNITCOMMON_ID=Z.UNITCOMMON_ID
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.POLICY_ID
AND D.UNITCOMMON_ID=E.UNITCOMMON_ID
AND E.POLICY_ID=Z.POLICY_ID
AND E.UNITCOMMON_ID=Z.UNITCOMMON_ID
AND E.HI_NBR_UNIQUE_CODE='CPU')

any ideas..

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.