sql - Inner SELECT in UPDATE not updating all rows Oracle -

sql - Inner SELECT in UPDATE not updating all rows Oracle -

i running on oracle 11 g. trying update column class in table1 using values in table2.classname. keys used bring together table1.t1id=table2.t2id table2 can have multiple classname values same id.

columns can used update are:

table1:

tid - identifier loaddt - date records loaded

table2:

t2id - identifier loaddt - date records loaded enrolldate - date updated when pupil enrolled in class. latest date have recent class enrolled in.

here update statement:

update table1 fs set class = (select a.classname table2 trim (a.t2id) = trim (fs.t1id) , to_date (a.loaddt, 'dd-mon-yy') = to_date ( (select max (loaddt) table2 aa trim (a.t2id) = trim (aa.t2id)), 'dd-mon-yy' , rownum = 1) , rownum = 1) to_date (loaddt, 'dd-mon-yy') = to_date ( (select max (loaddt) table1), 'dd-mon-yy');

my update seems leaving lot of rows null (90%) though table2.classname has no nulls.

i have not used enrolldate in update.

am missing something? can please help?

rownum = 1 can translated "choose quasi-random row". in many cases (such yours), you're lot improve off using analytic queries, if no other reason less error-prone.

this reply based on @multisync's solution, dislike using sub-queries in set clause. when updating correlated rows, prefer merge:

merge table1 fs using (select classname, t2id (select t2id, classname, row_number () on (partition t2id order loaddt desc) rw table2) a.rw = 1) b on (trim (b.t2id) = trim (fs.t1id)) when matched update set class = b.classname to_date (loaddt, 'dd-mon-yy') = to_date ( (select max (loaddt) table1), 'dd-mon-yy');

sql oracle