Difference between revisions of "INSERT .. VALUES"
From SQLZOO
| (2 intermediate revisions by one user not shown) | |||
| Line 1: | Line 1: | ||
| − | < | + | <h1>INSERT</h1> |
<p>The INSERT command is used to add a new row to a table.</p> | <p>The INSERT command is used to add a new row to a table.</p> | ||
<pre>INSERT INTO games(yr, city) VALUES (2012,'London')</pre> | <pre>INSERT INTO games(yr, city) VALUES (2012,'London')</pre> | ||
| Line 19: | Line 19: | ||
<p>You want to add the next Olympic games, in the year 2012, which will be held in London.</p> | <p>You want to add the next Olympic games, in the year 2012, which will be held in London.</p> | ||
<hr/> | <hr/> | ||
| − | <div class=' | + | <div class='ht'> |
| − | The INSERT statement adds a new row to the table: | + | <div class=params>schema:scott</div> |
| + | <source lang=sql class='tidy'> DROP TABLE games</source> | ||
| + | <source lang=sql class='setup'> CREATE TABLE games( | ||
| + | yr INTEGER PRIMARY KEY, | ||
| + | city VARCHAR(20)); | ||
| + | INSERT INTO games(yr,city) VALUES (2000,'Sydney'); | ||
| + | INSERT INTO games(yr,city) VALUES (2004,'Athens'); | ||
| + | INSERT INTO games(yr,city) VALUES (2008,'Beijing'); | ||
| + | </source> | ||
| + | The INSERT statement adds a new row to the table: | ||
| + | <source lang='sql' class='def e-oracle'> | ||
| + | INSERT INTO scott.games(yr,city) | ||
| + | VALUES (2012,'London'); | ||
| + | SELECT * FROM scott.games; | ||
| + | </source> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
INSERT INTO games(yr,city) | INSERT INTO games(yr,city) | ||
| Line 30: | Line 44: | ||
<p>See also</p> | <p>See also</p> | ||
<ul> | <ul> | ||
| − | <li>[[INSERT .. SELECT]] | + | <li>[[INSERT_.._SELECT |INSERT .. SELECT]] |
</ul> | </ul> | ||
<h2>What can go wrong</h2> | <h2>What can go wrong</h2> | ||
| − | <div class=' | + | <div class='ht'> |
Your INSERT statement may break some database rule such as the unique key requirement. | 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. | 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. | If you attempt to add a second row with 2008 for yr then you will get an error. | ||
| + | <source lang='sql' class='def e-oracle'> | ||
| + | INSERT INTO scott.games(yr,city) | ||
| + | VALUES (2008,'Paris'); | ||
| + | SELECT * FROM scott.games; | ||
| + | </source> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
INSERT INTO games(yr,city) | INSERT INTO games(yr,city) | ||
Latest revision as of 10:03, 17 July 2012
INSERT
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
| yr | city |
|---|---|
| 2000 | Sydney |
| 2004 | Athens |
| 2008 | Beijing |
The table games shows the year and the city hosting
the Olympic Games.
You want to add the next Olympic games, in the year 2012, which will be held in London.
schema:scott
DROP TABLE games
CREATE TABLE games( yr INTEGER PRIMARY KEY, city VARCHAR(20)); INSERT INTO games(yr,city) VALUES (2000,'Sydney'); INSERT INTO games(yr,city) VALUES (2004,'Athens'); INSERT INTO games(yr,city) VALUES (2008,'Beijing');
The INSERT statement adds a new row to the table:
INSERT INTO scott.games(yr,city) VALUES (2012,'London'); SELECT * FROM scott.games;
INSERT INTO games(yr,city) VALUES (2012,'London'); 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;
