17.05 - Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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';