INSERT .. VALUES

From SQLZoo
Jump to navigation Jump to search

The INSERT command is used to add a new row to a table.

INSERT INTO games(yr, city) VALUES (2012,'London')

The table is games
The column names are yr and city
Strings in the literal values must be quoted with single quotes.


Example

games
yr city
2012 London
2016 Rio
2020 Tokyo

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

You want to add the next Olympic games, in the year 2024, which will be held in Paris.


schema:scott
 DROP TABLE games
 CREATE TABLE games(
  yr INTEGER PRIMARY KEY,
  city VARCHAR(20));
INSERT INTO games(yr,city) VALUES (2012,'London');
INSERT INTO games(yr,city) VALUES (2016,'Rio');
INSERT INTO games(yr,city) VALUES (2020,'Tokyo');

The INSERT statement adds a new row to the table:

INSERT INTO scott.games(yr,city) VALUES (2024,'Paris');
SELECT * FROM scott.games;
INSERT INTO scott.games(yr,city) VALUES (2024,'Paris');
SELECT * FROM games;

See also

What can go wrong

Your INSERT statement may break some database rule such as the unique key requirement. In this example there is a primary key on year - that means that there may not be two rows with the same year. If you attempt to add a second row with 2008 for yr then you will get an error.

INSERT INTO scott.games(yr,city) 
VALUES (2008,'Paris');
SELECT * FROM scott.games;
INSERT INTO games(yr,city) 
VALUES (2008,'Paris');
SELECT * FROM games;

See also

DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects