SQLite for Beginners: UPDATE SET WHERE & the Danger of No WHERE | Episode 10

SQLite for Beginners: UPDATE SET WHERE & the Danger of No WHERE

In this tutorial, you will learn how to use the UPDATE statement in SQLite to modify existing rows in a table. We demonstrate updating single and multiple rows using the WHERE clause, changing column values with SET, and highlight the risks of omitting WHERE, which can unintentionally update every row.

Code

-- Create a table for student grades
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  subject TEXT,
  grade TEXT
);

-- Insert sample data
INSERT INTO students (name, subject, grade) VALUES
  ('Alice', 'Math', 'B'),
  ('Bob', 'Math', 'C'),
  ('Charlie', 'History', 'A');

-- Update a single student's grade
UPDATE students
SET grade = 'A'
WHERE name = 'Bob';

-- Update multiple rows: change subject name from 'Math' to 'Mathematics'
UPDATE students
SET subject = 'Mathematics'
WHERE subject = 'Math';

-- Danger: Update without WHERE updates all rows!
-- This will set every student's grade to 'F'
UPDATE students
SET grade = 'F';

-- To verify changes
SELECT * FROM students;

Key Points

• The UPDATE statement changes existing data in a table using SET to specify new values.
• Always use a WHERE clause to target specific rows; omitting it updates every row in the table.
• You can update one or multiple rows by defining conditions in the WHERE clause.
• Any column can be modified using SET, including text, numbers, or dates.
• Running SQL scripts with :!sqlite3 :memory: lets you test changes without affecting files.