Add rows with INSERT
Put new rows into a table with INSERT, one at a time or from another query. About 8 minutes.
INSERT adds a row
Until now you only read tables. INSERT INTO puts a new row in. Name the table, list the columns you are filling in brackets, and give the values in the same order after VALUES.
A column you leave out gets its default, usually NULL. Text values are in single quotes, as always. Give several rows in one statement by separating the brackets with commas.
INSERT INTO books (id, title, genre)
VALUES (4, 'Salt Road', 'Novel'),
(5, 'Paper Boats', 'Poetry');| id | title | genre |
|---|---|---|
| 1 | Gitanjali | Poetry |
| 2 | The Guide | Novel |
| 3 | Madhushala | Poetry |
The table starts with three books. INSERT doesn't touch them.
Copy rows, and what can go wrong
INSERT can also take rows from a query: INSERT INTO archive (id, title) SELECT id, title FROM books WHERE year < 1950. That's a copy, and it runs as one statement.
The table's rules still apply. A NOT NULL column that you leave out, or an id that already exists, makes the statement fail. Nothing is added, not even the rows before the bad one.
INSERT INTO archive (id, title) SELECT id, title FROM books WHERE year < 1950;
A table has a NOT NULL column title. An INSERT lists only id and genre. What happens?
- AThe INSERT fails and no row is added
- BThe row is added with title NULL
- CThe row is added with an empty title
Show the answer
The INSERT fails and no row is added. title can't be NULL and has no default, so the rule is broken.
Register a student
The students table has one row per student. Add one student with id 101, name Nikhil and branch CSE. Their cgpa isn't known yet, so leave it empty (NULL).
students(id, name, branch, cgpa)
Up to 2,000 students · id 101 is not used yet