Examples of Creating and Updating Views

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 */