CODEWITHSUNDEEP
sqloracle-database
ozzboy
ozzboy

Reputation: 2720

Inner SELECT in UPDATE not updating all rows Oracle

I am running this on Oracle 11 G. I am trying to UPDATE a column CLASS in TABLE1 from using values in TABLE2.CLASSNAME. Keys used to JOIN are TABLE1.T1ID=TABLE2.T2ID TABLE2 can have multiple CLASSNAME values for the same id.

Columns that can be used to UPDATE are:

TABLE1: 

TID - Identifier
LOADDT - Date all records were loaded

TABLE2: 

T2ID - Identifier
LOADDT - Date all records were loaded
ENROLLDATE - A date updated when the student enrolled in the class. The latest date will have the most recent class he enrolled in.

Here is my UPDATE statement:

UPDATE table1 fs
SET    class =
          (SELECT a.classname
           FROM   table2 a
           WHERE      TRIM (a.t2id) = TRIM (fs.t1id)
                  AND TO_DATE (a.loaddt, 'DD-MON-YY') =
                         TO_DATE ( (SELECT MAX (loaddt)
                                    FROM   table2 aa
                                    WHERE  TRIM (a.t2id) = TRIM (aa.t2id)),
                                  'DD-MON-YY' AND ROWNUM = 1)
                  AND ROWNUM = 1)
WHERE  TO_DATE (loaddt, 'DD-MON-YY') =
          TO_DATE ( (SELECT MAX (loaddt) FROM table1), 'DD-MON-YY');

My update seems to be leaving a lot of rows NULL (90%) even though the TABLE2.CLASSNAME has no NULLs.

I have not used the ENROLLDATE in my update.

Am I missing something? Can you please help?

Upvotes: 1

Views: 572

Answers (2)

Allan
Allan

Reputation: 17429

ROWNUM = 1 can almost always be translated to "choose a quasi-random row". In many cases (such as yours), you're a lot better off using analytic queries, if for no other reason than they are less error-prone.

This answer is based on @Multisync's solution, but I dislike using sub-queries in the SET clause. When updating correlated rows, I prefer MERGE:

MERGE INTO table1 fs
USING      (SELECT classname, t2id
            FROM   (SELECT t2id,
                           classname,
                           ROW_NUMBER ()
                              OVER (PARTITION BY t2id ORDER BY loaddt DESC)
                              rw
                    FROM   table2) a
            WHERE  a.rw = 1) b
ON         (TRIM (b.t2id) = TRIM (fs.t1id))
WHEN MATCHED THEN
   UPDATE SET
      class = b.classname
      WHERE      TO_DATE (loaddt, 'DD-MON-YY') =
                    TO_DATE ( (SELECT MAX (loaddt) FROM table1), 'DD-MON-YY');

Upvotes: 1

Multisync
Multisync

Reputation: 8797

UPDATE TABLE1 fs
SET  CLASS=
    (
        select a.CLASSNAME from (
            select T2ID, LOADDT, CLASSNAME, 
                   row_number() over(partition by T2ID order by LOADDT desc) rw  
            from TABLE2) a 
        where a.rw = 1 and TRIM(a.T2ID) = TRIM(fs.T1ID)
    )
-- do you really need this condition? 
WHERE TO_DATE(LOADDT,'DD-MON-YY')=TO_DATE((SELECT MAX(LOADDT) FROM TABLE1),'DD-MON-YY');

Upvotes: 1

Related Questions