CODEWITHSUNDEEP
sqloracle-database
dlee
dlee

Reputation: 11

Avoiding multiple sub queries

I'm trying to update a couple of columns in a table using another record in the same table. Following is the SQL that I came up with but I'm wondering how I can avoid multiple sub queries that will return the same record. I'm working on Oracle 11gR2.

UPDATE
  IFTBL E
SET
  E.ATT_CREATED = (SELECT A.CREATED FROM ATT A WHERE A.ROW_ID = E.T_ACTIVITYA__RID),
  E.ATT_CREATED_BY = (SELECT B.CREATED_BY FROM ATT B WHERE B.ROW_ID = E.T_ACTIVITYA__RID)
WHERE
  E.IF_ROW_BATCH_NUM = BATCH_NO_IN AND E.IF_ROW_STAT = 'EXPORTED' AND E.ATT_FILE_SRC_TYPE = 'FILE';

Upvotes: 1

Views: 82

Answers (3)

Boneist
Boneist

Reputation: 23578

In addition to the other answers, you could simply do a multi-column update:

UPDATE
  IFTBL E
SET
  (E.ATT_CREATED, E.ATT_CREATED_BY) = (SELECT A.CREATED,
                                              A.CREATED_BY
                                       FROM   ATT A
                                       WHERE  A.ROW_ID = E.T_ACTIVITYA__RID)
WHERE
  E.IF_ROW_BATCH_NUM = BATCH_NO_IN
  AND E.IF_ROW_STAT = 'EXPORTED'
  AND E.ATT_FILE_SRC_TYPE = 'FILE';

Upvotes: 0

Michael Piankov
Michael Piankov

Reputation: 1997

If ROW_ID is primary key in ATT and T_ACTIVITYA__RID is foreign key on IFTBL with reference to ROW_ID you may write update (select ...)

update (select E.ATT_CREATED, E.ATT_CREATED_BY  
               A.CREATED , A.CREATED_BY 
          from IFTBL E, ATT A 
         where E.T_ACTIVITYA__RID = A.ROW_ID(+)
           AND E.IF_ROW_BATCH_NUM = BATCH_NO_IN 
           AND E.IF_ROW_STAT = 'EXPORTED' 
           AND E.ATT_FILE_SRC_TYPE = 'FILE')
SET ATT_CREATED = CREATED, 
    ATT_CREATED_BY = CREATED_BY ;

Upvotes: 0

Esty
Esty

Reputation: 1912

You can use Merge.

    MERGE INTO IFTBL 
    USING 
    (
      SELECT CREATED,CREATED_BY,ROW_ID 
      FROM ATT 
    ) A ON (A.ROW_ID = IFTBL.T_ACTIVITYA__RID)
    WHEN MATCHED THEN UPDATE 
        SET 
        IFTBL.ATT_CREATED = A.CREATED, 
        IFTBL.ATT_CREATED_BY = A.CREATED_BY
    WHERE
    IFTBL.IF_ROW_BATCH_NUM = BATCH_NO_IN 
    AND IFTBL.IF_ROW_STAT = 'EXPORTED' 
    AND IFTBL.ATT_FILE_SRC_TYPE = 'FILE'

Upvotes: 1

Related Questions