Oracle UPDATE with INNER JOIN

Question

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?

None of the queries should run. They all have a missing closing parenthesis at the end. Are these really the queries that you are running? — sstan, Jul 09 '15 at 15:29
That was a mistake on my part when copy pasting. It has been fixed. Yes those are the queries I am running except the data has been modified for example purposes. — Alexandre Thenorio, Jul 09 '15 at 15:31

sstan · Accepted Answer · 2015-07-09T17:00:21.023

3

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.

edited Jul 09 '15 at 17:00

answered Jul 09 '15 at 15:33

sstan

35,425
6
48
66

Thank you. I have not worked with SQL in a little while and I expected I was just over complicating things. It looks like your query is much more elegant to solve the issue. What about if I want to set some default value in the case newTable has some home entry not found in topten? – Alexandre Thenorio Jul 09 '15 at 15:40
I edited my answer to include the `COALESCE` function to allow you to specify a default value. – sstan Jul 09 '15 at 15:49

Oracle UPDATE with INNER JOIN

1 Answers1