CODEWITHSUNDEEP
sqloracle-databasejoinsql-update
Alexandre Thenorio
Alexandre Thenorio

Reputation: 2418

Oracle UPDATE with INNER JOIN

I need to update the values of a table column from the MAX value of another table column.

This question is exactly my case where I have a table I need to get the MAX value from and then update the column called datetime of table newTable with the MAX datetime value of that table for all matching home values in newTable.

Based on the data from the linked thread, I have come up with the following update statement

UPDATE newTable s
SET s.datetime = (
    SELECT tt.timedate
    FROM topten tt
    INNER JOIN
        (SELECT home, MAX(datetime) AS MaxDateTime
        FROM topten
        GROUP BY home) groupedtt 
    ON tt.home = groupedtt.home 
    AND tt.datetime = groupedtt.MaxDateTime WHERE s.home = tt.home);

The problem with this is that I get the following error

SQL Error: ORA-01427: single-row subquery returns more than one row

I should also point out that topten.home is not unique while newTable.home is.

I can get rid of the error by adding a rownum statement like so:

UPDATE newTable s
SET s.datetime = (
    SELECT tt.timedate
    FROM topten tt
    INNER JOIN
        (SELECT home, MAX(datetime) AS MaxDateTime
        FROM topten
        GROUP BY home) groupedtt 
    ON tt.home = groupedtt.home 
    AND tt.datetime = groupedtt.MaxDateTime WHERE s.home = tt.home AND rownum <= 1);

or setting the subquery for MAX

UPDATE newTable s
SET s.datetime = (
    SELECT MAX(tt.timedate)
    FROM topten tt
    INNER JOIN
        (SELECT home, MAX(datetime) AS MaxDateTime
        FROM topten
        GROUP BY home) groupedtt 
    ON tt.home = groupedtt.home 
    AND tt.datetime = groupedtt.MaxDateTime WHERE s.home = tt.home);

however I don't quite understand why this is needed since the MAX statement in the original subquery should make sure there is only 1 entry per home nor do I know what the impact of those changes are exactly (Though initial tests suggest they seem to work)

Am I over complicating it?

Upvotes: 3

Views: 4905

Answers (1)

sstan
sstan

Reputation: 36473

Why not simply...

UPDATE newTable s
SET s.datetime = (
    SELECT COALESCE(MAX(tt.timedate), <put your default date here>)
    FROM topten tt
    WHERE s.home = tt.home)

If I take your original statement, and I remove the inner join, like this:

UPDATE newTable s
SET s.datetime = (
    SELECT tt.timedate
    FROM topten tt
    WHERE s.home = tt.home);

... you will see that subquery can return multiple rows for the same home value. So let's say that the above returns 5 rows per home value, and then you add your inner join with the MAX and GROUP BY query which does return a single row per home, it will still return a total of 5 x 1 rows. It won't magically reduce the number of rows to 1.

Upvotes: 3

Related Questions