Learn SQL Database Programming

Updating data using a view

To update parkalias to NULL where parkkey = 'ANA01', you can execute the following query:

UPDATE parksaliasSET parkalias = NULLWHERE parkkey = 'ANA01';

You will receive the following error:

Error Code: 1369. CHECK OPTION failed 'lahmansbaseballdb.parksalias'

You can't update parkalias to NULL because NULL is filtered out in the WHERE clause of the view, and the view has WITH CHECK OPTION defined.

Now, let's create a view as shown in the following code:

USE lahmansbaseballdb; CREATE VIEW parksalias ASSELECT parkalias, parkkey, parkname,        city, state, countryFROM parks_copyWHERE parkalias IS NOT NULL; 

You will be able to update a parkalias value that is NULL, even though the WHERE clause filters to IS NOT NULL ...