Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause - Teradata Vantage - Analytics Database

The following UPDATE operation uses a subquery to decrease the list price on an electroacoustic CD budget line for labels containing the string ‘erstwhile’:

     UPDATE disc
     SET price = price * .85
     WHERE label_no IN (SELECT label_no
                        FROM label
                        WHERE label_name LIKE '%erstwhile%')
                        AND   line = 'NEA';

You can obtain the same result by writing the query using a join between the disc and label tables on label_no:

     UPDATE disc
     SET price = price * .85
     WHERE disc.label_no = label.label_no
     AND   label_name LIKE '%erstwhile%'
     AND   line = 'NEA';