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?

Answers


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.


Need Your Help

Form not being checked

javascript php jquery ajax

I want there to be server side checks within my code. Once the form is submitted, I should have the form checked server side and the error should be echoed back. But as it is now, no matter what I ...

Is there a way to slowdown mysql query for lower cpu usage?

mysql query-optimization cpu-usage

I am ordering INT rows by DESC on a table with 300k rows and selecting @rownum:=@rownum+1 to update the same row's RANK field.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.