You are here: SQL Development > Using Updatable Views > Examples of Creating and Updating Views

Examples of Creating and Updating Views

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.

Creating and Updating a View

Create a view to be used in the examples below and then update that view:

USE hockey;
CREATE VIEW vw_some_players AS
SELECT playerid, firstname, lastname
FROM players
WHERE firstname like 'Sp%';
 
SELECT * FROM vw_some_players ORDER BY playerid;
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 cleghsp01  Sprague   Cleghorn 
 machasp01  Spencer   Machacek 
 moynesp01  Speed     Moynes   
 tatchsp01  Spence    Tatchell 
 vailsp01   Sparky    Vail     
 
UPDATE vw_some_players SET lastname = lastname||'_updated' LIMIT 1;
 
SELECT * FROM vw_some_players ORDER BY playerid;
 PLAYERID  FIRSTNAME      LASTNAME    
 --------- ---------- ----------------
 cleghsp01  Sprague   Cleghorn_updated
 machasp01  Spencer   Machacek        
 moynesp01  Speed     Moynes          
 tatchsp01  Spence    Tatchell        
 vailsp01   Sparky    Vail

Removing a Row from the View But Not the Table

Update a view to remove that row from the view, based on the view criteria. This does not remove the row from the table.

UPDATE vw_some_players SET firstname = 'updated_'||firstname LIMIT 1;
 
/* should not be in view anymore */
SELECT * FROM vw_some_players ORDER playerid;
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 machasp01  Spencer   Machacek 
 moynesp01  Speed     Moynes   
 tatchsp01  Spence    Tatchell 
 vailsp01   Sparky    Vail     
 
/* but should be in table showing update */
SELECT playerid, firstname, lastname FROM players WHERE firstname like 'updated%' ORDER BY playerid;
 PLAYERID     FIRSTNAME        LASTNAME    
 --------- --------------- ----------------
 cleghsp01 updated_Sprague Cleghorn_updated

Removing a Row from a View And the Table

The following example uses a view to delete a row. The row to be deleted must be in the view criteria for the row to be deleted from the table. Otherwise, the row remains in the table.

SELECT * FROM vw_some_players WHERE firstname = 'Spencer';
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 machasp01  Spencer   Machacek  

DELETE FROM vw_some_players WHERE firstname = 'Spencer';
 
/* row is no longer included in view */
SELECT * FROM vw_some_players WHERE firstname = 'Spencer';
  /* No Records Selected */
 
/* row has also been removed from table */
SELECT * FROM players WHERE firstname = 'Spencer';
  /* No Records Selected */
 
/* Deleting a table row that is not included in the criteria of the view will not delete the row from the table */
SELECT playerid, firstname, lastname FROM players WHERE firstname like 'Ow%';
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 fusseow01    Owen     Fussey  
 lloydow01    Owen     Lloyd   
 nolanow01    Owen     Nolan   
 
DELETE FROM vw_some_players WHERE firstname like 'Ow%';
/* should still be in table since this record is not included in the view criteria */
SELECT playerid, firstname, lastname FROM players WHERE firstname like 'Ow%';
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 fusseow01    Owen     Fussey  
 lloydow01    Owen     Lloyd   
 nolanow01    Owen     Nolan

Update a View to Add Rows to the Table

The following example uses a view to insert rows into the table. NuoDB does not check view criteria when you insert a row into a table. This means you can insert a row into the table regardless of whether that row would be included in the view. When you try to insert a row, NuoDB does perform underlying table constraint checking.

INSERT INTO vw_some_players (playerid,firstname,lastname) VALUES ('SplastSp01','Spfirst','Splast');
SELECT * FROM vw_some_players ORDER BY playerid;
  PLAYERID  FIRSTNAME  LASTNAME 
 ---------- ---------- ---------
 SplastSp01  Spfirst   Splast   
 moynesp01   Speed     Moynes   
 tatchsp01   Spence    Tatchell 
 vailsp01    Sparky    Vail     
 
/* Insert via view even though insert values do not match view criteria is allowed */
INSERT INTO vw_some_players (playerid,firstname,lastname) VALUES ('playerid','firstname','lastname');


/* inserted record will not be included in the view only the table */
SELECT * FROM vw_some_players WHERE playerid = 'playerid';
  /* No Records Selected */

SELECT playerid, firstname, lastname FROM players WHERE playerid = 'playerid';
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 playerid  firstname  lastname

Trying to Update a Non-Updatable View

The following example tries to update a view that is not updatable due to an ORDER BY clause:

DROP VIEW IF EXISTS vw_players_not_updatable;
CREATE VIEW vw_players_not_updatable AS SELECT playerid, firstname, lastname FROM players WHERE firstname IN ('Cy','Roberto') ORDER BY playerid;
SELECT * FROM vw_players_not_updatable;
 PLAYERID  FIRSTNAME  LASTNAME 
 --------- ---------- ---------
 dennecy01  Cy        Denneny  
 luongro01  Roberto   Luongo   
 romanro01  Roberto   Romano   
 thomacy01  Cy        Thomas   
 wentwcy01  Cy        Wentworth

UPDATE vw_players_not_updatable SET lastname = lastname||'1' ;
/*  ERROR: Attempted update to a non-updatable view HOCKEY.VW_PLAYERS_NOT_UPDATABLE */