where u.name in (select state from sql.newpop);
select Name format=$17., Capital format=$15.,
Population, Area, Continent format=$13., Statehood format=date9.
Output 6.22 Sql.UnitedStates with Updated Population Data (Partial Output)
How It Works
The UPDATE statement updates values in the Sql.UnitedStates table (here with the alias
U). For each row in the Sql.UnitedStates table, the in-line view in the SET clause returns
a single value. For rows that have a corresponding row in Sql.NewPop, this value is the
value of the Population column from Sql.NewPop. For rows that do not have a
corresponding row in Sql.NewPop, this value is missing. In both cases, the returned
value is assigned to the Population column.
The WHERE clause ensures that only the rows in Sql.UnitedStates that have a
corresponding row in Sql.NewPop are updated by checking each value of Name against
the list of state names that is returned from the in-line view. Without the WHERE clause,
rows that do not have a corresponding row in Sql.NewPop would have their Population
values updated to missing.
Creating and Using Macro Variables
You want to create a separate data set for each unique value of a column.
Creating and Using Macro Variables 205