CODEWITHSUNDEEP
oracle-database
bbunyanngwiri
bbunyanngwiri

Reputation: 11

how to make an update statment more optimal

i have to update 10 different fields in 4 different tables on a regular basis

this is the script for 1 field in 1 table:

UPDATE MCA_table
SET REAL_RGUS = 'Y'
WHERE PART_NUMBER IN 
      (SELECT DISTINCT MCA.PART_NUMBER
      FROM MCA_table MCA
      INNER JOIN refP_table REFP ON REFP.POID = MCA.PART_NUMBER
      WHERE MCA.REAL_RGUS IS NULL AND REFP.REAL_RGUS = 'Y');

i created a stored procedure based on the above script that does all the above updates sequentially but having run for about 50 minutes, it is still going and am not sure if i have written it in the most optimal way; i may be trying to be too clever

help appreciated :)

Upvotes: 1

Views: 25

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

It looks like you might be trying to do:

UPDATE MCA_table
SET REAL_RGUS = 'Y'
WHERE REAL_RGUS IS NULL
AND PART_NUMBER IN (SELECT POID
    FROM refP_table
    WHERE REAL_RGUS = 'Y')

But that would depend on the schema and data. So don't use unless you are sure it is correct for your situation.

Upvotes: 2

Related Questions