INSERT .. SELECT

From SQLZOO
Revision as of 21:10, 22 October 2012 by Peter (talk | contribs) (Reverted edits by Miacardenas (talk) to last revision by Connor)
Jump to navigation Jump to search

INSERT .. SELECT

The table games shows the year and the city hosting the Olympic Games.

games
yrcity
2000Sydney
2004Athens
2008Beijing
schema:scott
 DROP TABLE games
 CREATE TABLE games(
  yr INTEGER,
  city VARCHAR(20));
INSERT INTO games VALUES (2000,'Sydney');
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2008,'Beijing');

The INSERT SELECT statement adds a new row to the table based on a SELECT statement: In this example you run the next three Olympic games in the same three venues:

INSERT INTO scott.games(yr,city)
  SELECT yr+12, city FROM scott.games;
SELECT * FROM scott.games;
INSERT INTO games(yr,city)
  SELECT yr+12, city FROM games;
SELECT * FROM games;

See also