How can I speed up a joined update in SQL? My statement seems to run indefinitely
I have two tables: a source table and a target table. The target table will have a subset of the columns of the source table. I need to update a single column in the target table by joining with the source table based on another column. The update statement is as follows:
UPDATE target_table tt SET special_id = ( SELECT source_special_id FROM source_table st WHERE tt.another_id = st.another_id )
For some reason, this statement seems to run indefinitely. The inner select happens almost immediately when executed by itself. The table has roughly 50,000 records and its hosted on a powerful machine (resources are not an issue).
Am I doing this correctly? Any reasons the above wouldn't work in a timely manner? Any better way to do this?
Your initial query executes the inner subquery once for every row in the outer table. See if Oracle likes this better:
UPDATE target_table SET special_id = st.source_special_id FROM target_table tt INNER JOIN source_table st WHERE tt.another_id = st.another_id
(edited after posted query was corrected)
Add: If the join syntax doesn't work on Oracle, how about:
UPDATE target_table SET special_id = st.source_special_id FROM target_table tt, source_table st WHERE tt.another_id = st.another_id
The point is to join the two tables rather than using the outer query syntax you are currently using.