How to Insert, Update, and Delete Data

-- Create table
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'pending',
  priority INTEGER DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- INSERT: Add single row
INSERT INTO tasks (title, description, priority)
VALUES ('Buy groceries', 'Milk, eggs, bread', 2);

-- INSERT: Add multiple rows
INSERT INTO tasks (title, description, priority) VALUES
  ('Clean house', 'Vacuum and mop', 3),
  ('Pay bills', 'Electricity and internet', 1),
  ('Call doctor', 'Schedule appointment', 2);

SELECT * FROM tasks;

-- UPDATE: Modify single row
UPDATE tasks SET status = 'completed' WHERE id = 1;

-- UPDATE: Modify multiple rows
UPDATE tasks SET priority = 2 WHERE status = 'pending';

-- UPDATE: Multiple columns
UPDATE tasks 
SET status = 'in-progress', priority = 3
WHERE title = 'Clean house';

SELECT * FROM tasks;

-- DELETE: Remove single row
DELETE FROM tasks WHERE id = 4;

-- DELETE: Remove multiple rows
DELETE FROM tasks WHERE status = 'completed';

-- DELETE: Remove all rows (keep table)
-- DELETE FROM tasks;

-- Safe update with WHERE clause
-- Always use WHERE to avoid updating/deleting all rows!
UPDATE tasks SET priority = 1 WHERE priority IS NULL;

SELECT * FROM tasks;

-- INSERT OR REPLACE (upsert)
INSERT OR REPLACE INTO tasks (id, title, priority)
VALUES (2, 'Clean house (updated)', 2);

SELECT * FROM tasks;