INSERT, UPDATE, DELETE: modifying data

Querying data is not enough: you also need to be able to add, correct, and delete it. Three SQL statements cover these needs.

Adding a row with INSERT

INSERT INTO eleves (id, nom, prenom, note)
VALUES (4, 'Bernard', 'Yanis', 14.0);

This statement adds a new student to the table. You specify the column names, then the corresponding values in the same order. The id primary key must be different from the ones already used, otherwise the DBMS refuses the insertion.

Correcting a value with UPDATE

UPDATE eleves
SET note = 16.0
WHERE id = 1;

UPDATE modifies one or more columns of the rows that satisfy the WHERE condition. Here, only the grade of the student whose identifier is 1 (Lucie Martin) is modified.

Warning: an UPDATE without WHERE modifies all the rows of the table. This is one of the most dreaded classic mistakes in SQL.

Deleting a row with DELETE

DELETE FROM eleves
WHERE id = 2;

This query deletes the student whose identifier is 2 (Karim Dubois). As with UPDATE, a DELETE without WHERE deletes all the rows of the table, with no possibility of immediate undo. This is why it is always best to check with a SELECT and the same condition before running a DELETE or an UPDATE, to be sure of only affecting the intended rows.