Avoid multiple SELECT while updating a table's column relatively to another table's one

Question

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

Answer 1

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.