1

I am quite a newbie with SQL queries but I need to modify a column of a table relatively to the column of another table. For now I have the following query working:

UPDATE table1
SET date1=(
    SELECT last_day(max(date2))+1
    FROM table2
    WHERE id=123
    )
WHERE id=123
  AND date1=to_date('31/12/9999', 'dd/mm/yyyy');

The problem with this structure is that, I suppose, the SELECT query will be executed for every line of the table1. So I tried to create another query but this one has a syntax error somewhere after the FROM keyword:

UPDATE t1
SET t1.date1=last_day(max(t2.date2))+1
FROM table1 t1
INNER JOIN table2 t2
        ON t1.id=t2.id
WHERE t1.id=123
  AND t1.date1=to_date('31/12/9999', 'dd/mm/yyyy');

AND besides that I don't even know if this one is faster than the first one...

Do you have any idea how I can handle this issue?

Thanks a lot!

Kind regards,

Julien

Julien Camus
  • 43
  • 1
  • 4

2 Answers2

0

The first code you wrote is fine. It won't be executed for every line of the table1 as you fear. It will do the following:

  • it will run the subquery to find a value you want to use in your UPDATE statement, searching through table2, but as you have stated the exact id from the table, it should be as fast as possible, as long as you have created an index on that (I guess a primary key) column
  • it will run the outer query, finding the single row you want to update. As before, it should be as fast as possible as you have stated the exact id, as long as there is an index on that column

To summarize, If those ID's are unique, both your subquery and your query should return only one row and it should execute as fast as possible. If you think that execution is not fast enough (at least that it takes longer than the amount of data would justify) check if those columns have unique values and if they have unique indexes on them.

In fact, it would be best to add those indexes regardless of this problem, if they do not exist and if these columns have unique values, as it would drastically improve all of the performances on these tables that search through these id columns.

Goran Kutlaca
  • 2,014
  • 1
  • 12
  • 18
  • Thank you for your detailed explanation! Actually I have several lines with this same id column so that is why I was afraid it looks for the max date several times. Do I need to find a way to store the max date value once, such as a "global variable", or do you recommend something else? Thanks again! – Julien Camus Nov 30 '18 at 13:30
  • No Oracle is smart enough to know that the inner scalar query will only have one value, so it will compute it once and reuse that value even if table1 has several record with the same id. – Sentinel Nov 30 '18 at 23:00
  • Nice! Thank you for your expertise! I accept this answer then! – Julien Camus Dec 03 '18 at 16:06
0

Please try to use MERGE

MERGE INTO ( 
    SELECT id, 
           date1 
      FROM table1 
     WHERE date1 = to_date('31/12/9999', 'dd/mm/yyyy')
       AND id = 123
) t1
USING (
    SELECT id,
           last_day(max(date2))+1 max_date
    FROM table2
    WHERE id=123
    GROUP BY id
) t2 ON (t1.id = t2.id)
WHEN MATCHED THEN
  UPDATE SET t1.date1 = t2.max_date
;
RGruca
  • 204
  • 1
  • 5
  • Thank you for your answer. I tried this query but it was slower than the first one (in my example 173 seconds versus 77 seconds before). I have several rows with the same id, maybe that is why it is slower? – Julien Camus Nov 30 '18 at 13:20
  • You should not look at execution time, but rather on number of buffer gets. However, if you want relay on execution time then please execute this few times, refuse time of first execution and calculate average. – RGruca Nov 30 '18 at 15:59
  • I did what you suggested and the query above is slightly better in execution time so I will keep this one. Thank you again, it is always good to have several approaches! – Julien Camus Dec 03 '18 at 16:05