Avoiding multiple sub queries

Question

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';

Esty · Accepted Answer · 2016-10-25 05:43:39Z

1

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'

edited Oct 25, 2016 at 5:43

answered Oct 25, 2016 at 5:37

Esty

1,8923 gold badges17 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Piankov · Accepted Answer · 2016-10-25 06:31:42Z

0

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 ;

answered Oct 25, 2016 at 6:31

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

Comments

Boneist · Accepted Answer · 2016-10-25 08:23:35Z

0

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';

answered Oct 25, 2016 at 8:23

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Avoiding multiple sub queries

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related