CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Michelle Daniel
Michelle Daniel

Reputation: 165

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.

Upvotes: 0

Views: 416

Answers (2)

Prosenjit
Prosenjit

Reputation: 1

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

Upvotes: 0

wolφi
wolφi

Reputation: 8361

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.

Upvotes: 2

Related Questions