sql - Oracle UPDATE with INNER JOIN -

i need update values of table column max value of table column.

this question case have table need max value , update column called datetime of table newtable max datetime value of table matching home values in newtable.

based on data linked thread, have come following update statement

update newtable s set s.datetime = (     select tt.timedate     topten tt     inner join         (select home, max(datetime) maxdatetime         topten         group home) groupedtt      on tt.home = groupedtt.home      , tt.datetime = groupedtt.maxdatetime s.home = tt.home); 

the problem following error

sql error: ora-01427: single-row subquery returns more 1 row 

i should point out topten.home not unique while newtable.home is.

i can rid of error adding rownum statement so:

update newtable s set s.datetime = (     select tt.timedate     topten tt     inner join         (select home, max(datetime) maxdatetime         topten         group home) groupedtt      on tt.home = groupedtt.home      , tt.datetime = groupedtt.maxdatetime s.home = tt.home , rownum <= 1); 

or setting subquery max

update newtable s set s.datetime = (     select max(tt.timedate)     topten tt     inner join         (select home, max(datetime) maxdatetime         topten         group home) groupedtt      on tt.home = groupedtt.home      , tt.datetime = groupedtt.maxdatetime s.home = tt.home); 

however don't quite understand why needed since max statement in original subquery should make sure there 1 entry per home nor know impact of changes (though initial tests suggest seem work)

am on complicating it?

why not simply...

update newtable s set s.datetime = (     select coalesce(max(tt.timedate), <put default date here>)     topten tt     s.home = tt.home) 

---

if take original statement, , remove inner join, this:

update newtable s set s.datetime = (     select tt.timedate     topten tt     s.home = tt.home); 

... see subquery can return multiple rows same home value. let's above returns 5 rows per home value, , add inner join max , group by query return single row per home, still return total of 5 x 1 rows. won't magically reduce number of rows 1.