UPDATE and DELETE

Change and remove rows — always with a WHERE clause.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗

main.sql

UPDATE enrollments SET grade = 8.5 WHERE grade IS NULL;
DELETE FROM enrollments WHERE grade < 8;

SELECT count(*) AS rows_left, sum(grade) AS total FROM enrollments;

setup/schema.sql

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

rows_left	total
4	35

Uses the school database in setup/schema.sql (shown below the query): students, courses and enrollments. The UPDATE and the DELETE each report “1 row changed”.

Watch out: Without WHERE, UPDATE and DELETE change every row of the table. Run the same WHERE as a SELECT first to see which rows it matches.

Insert or update in one statement with INSERT … ON CONFLICT (id) DO UPDATE SET … (an “upsert”).

UPDATE and DELETE

Related topics

Official references