SQL for Data Analytics · Changing the data
Changing the data
INSERT, UPDATE, DELETE11 min
A note on this video
Workbench stopped my DELETE until I turned off safe updates in its preferences. Safe updates is meant to stop an UPDATE or DELETE that doesn't pick its rows by a key column, and this students table doesn't have a key yet. Keys come in the Constraints lesson. The boxes here don't have a safe update mode, so an UPDATE or DELETE runs without that step.
The video starts with the script for the students table from the CREATE, ALTER, DROP lesson, and the box below starts with that script too. CREATE DATABASE IF NOT EXISTS makes college only if you don't have it yet. Then the script drops students, if it's there, and creates it again, empty, with the five columns from the video. Run it to start where the video starts.
Run queries here while you watch. When I tell you in the video to open Workbench and try something, use this box. It runs on your own MySQL server, the same one as every box in this unit's lessons, and whatever you run stays. This is for your own practice. It isn't recorded or part of your unit submission.
An INSERT doesn't have a WHERE, but the UPDATE in the video needed one. Why?
How I'd answer it
An INSERT adds a new row, so there's no row in the table for it to pick. An UPDATE or a DELETE works on rows that are already in the table, and the WHERE says which ones. Without a WHERE, it works on every row. My UPDATE would have made every student Sherry Dorian.
An INSERT with only VALUES gives a value for every column, in the order the columns are in the table.
INSERT INTO students
VALUES (1, 'Jane', 'Doe', 25, 'jane@mail.com');
If a value is missing, the database can't tell which value goes in which column. It refuses the row with Column count doesn't match value count at row 1.
To leave a column empty, put NULL in its place, or name the columns you're giving values for. A column you leave out of the list gets NULL.
INSERT INTO students (ID, FirstName, LastName, Age)
VALUES (2, 'Sherry', 'Dorian', 22);
Text goes in single quotes. Numbers and NULL don't. When the text has an apostrophe in it, like the name O'Neil, type the apostrophe twice: 'O''Neil'. A single one would end the text early.
One INSERT can add several rows, with a comma between them.
INSERT INTO students
VALUES (3, 'Michael', 'Ross', 30, 'michael@mail.com'),
(4, 'John', 'Doe', 19, 'john@mail.com');
The box starts with the script from the video, which makes students again, empty. Add INSERT statements under it that put in these three students, then run the whole box.
| ID | FirstName | LastName | Age | |
|---|---|---|---|---|
| 1 | Jane | Doe | 25 | jane@mail.com |
| 2 | Sherry | Dorian | 22 | sherry@mail.com |
| 3 | Liam | O'Neil | 31 | NULL |
UPDATE and DELETE work on rows that are already in a table. Their WHERE works the same as a SELECT's. It picks rows, and the statement changes only those rows.
Pick a statement and a WHERE, and see which rows of students they act on.
Which rows a WHERE picks
students before
Since the WHERE is the same, you can try it before you change anything. Write a SELECT with the WHERE you plan to use, run it, and look at the rows it returns. If those are the rows you mean, change the SELECT into the UPDATE or DELETE.
SELECT *
FROM students
WHERE ID = 12;
DELETE FROM students
WHERE ID = 12;
Partway through the INSERT, UPDATE, DELETE video, the students table had a Jane Doe in it four times. Two of those rows were meant to be other students, and one shouldn't have been there at all.
The box sets up students with rows like the ones in the video. Add UPDATE and DELETE statements under the script so that students ends up as the table below, then run the whole box.
| ID | FirstName | LastName | Age | |
|---|---|---|---|---|
| 1 | Jane | Doe | 25 | jane@mail.com |
| 2 | Sherry | Dorian | 22 | sherry@mail.com |
| 3 | Michael | Ross | 30 | michael@mail.com |
| 4 | John | Doe | 19 | john@mail.com |
The rows with ID 2 and ID 3 are already in the table, so they need an UPDATE each, with a WHERE that picks only that row. One UPDATE can set several columns, with commas between them: SET FirstName = 'Sherry', LastName = 'Dorian'.