Michelle Daniel Michelle Daniel - 5 months ago 6
SQL Question

Oracle 11g - Updating matched rows with values from a source table

T1.LEID needs to be UPDATED with T1.ID values from
Rows in which both the MSID and the ESTID match.

I have turned my syntax around and tried several variations, but always end up with same error 01427.

UPDATE T1
SET LEID = (SELECT T2.id
FROM T2
WHERE T2.MSID = T1.MSID )
WHERE EXISTS (SELECT T2.id
FROM T2
WHERE T2.ESTID = T1.ESTID
);

ORA-01427: single-row subquery returns more than one row


{ CREATE TABLE T1
(
{

ESTID NUMBER(8),
MSID NUMBER(8),
SMID NUMBER(8),
LEID NUMBER(8)
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(545, 5454, 888, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(334, 5555, 6541, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(254, 4487, 888, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(659, 54518, 654, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(854, 8548, 85478, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(658, 12485, 254, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(444, 5555, 888, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(444, 5545, 888, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(444, 4444, 888, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(555, 5555, 999, NULL);
Insert into T1
(ESTID, MSID, SMID, LEID)
Values
(444, 3333, 5, NULL);
COMMIT; }

CREATE TABLE T2
(
ESTID NUMBER(8),
MSID NUMBER(8),
ID NUMBER(8)
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING; }

Insert into T2


{(ESTID, MSID, ID)
Values
(545, 5454, 14);
Insert into T2
(ESTID, MSID, ID)
Values
(334, 5555, 16);
Insert into T2
(ESTID, MSID, ID)
Values
(254, 4487, 18);
Insert into T2
(ESTID, MSID, ID)
Values
(659, 54518, 20);
Insert into T2
(ESTID, MSID, ID)
Values
(854, 8548, 22);
Insert into T2
(ESTID, MSID, ID)
Values
(658, 12485, 24);
Insert into T2
(ESTID, MSID, ID)
Values
(444, 5555, 26);
Insert into T2
(ESTID, MSID, ID)
Values
(444, 5545, 28);
Insert into T2
(ESTID, MSID, ID)
Values
(444, 4444, 30);
Insert into T2
(ESTID, MSID, ID)
Values
(555, 5555, 32);
Insert into T2
(ESTID, MSID, ID)
Values
(444, 3333, 34);
COMMIT; }


Would appreciate any help or suggestions on how to alter the scriupt top update the ropws in T1.

Answer

If both columns, MSID and ESTID need to match, the following should do the trick:

UPDATE T1
   SET LEID = (SELECT T2.id
                 FROM T2
                WHERE T2.MSID = T1.MSID AND T2.ESTID = T1.ESTID)
 WHERE EXISTS (SELECT T2.id
                 FROM T2
                WHERE T2.MSID = T1.MSID AND T2.ESTID = T1.ESTID);     
11 rows updated.
Comments