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 the case where I have a table where I need to get the MAX value and then update the datetime column of the newTable with the MAX datetime value of that table for all matching original values in the newTable.
Based on the data from the linked thread, I came 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 is I am getting 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 but newTable.home.
I can get rid of the error by adding a rownum 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 AND rownum <= 1);
or setting a 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 necessary, as the MAX statement in the original subquery has to make sure there is only 1 record for each house, and I don't know what the impact of these changes is (although initial tests suggest they seem to work)
Am I complicating it?
source to share
Why not just ...
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 expression and I remove the inner join like:
UPDATE newTable s
SET s.datetime = (
SELECT tt.timedate
FROM topten tt
WHERE s.home = tt.home);
... you will see that a subquery can return multiple rows for the same value home
. So let's say the above returns 5 rows per home
value, and then you add your inner join to the query MAX
and GROUP BY
that returns one row per home
value, it will still return a total of 5 x 1
rows. Will not magically reduce the number of lines to 1
.
source to share