INSERT, UPDATE, DELETE
Writing data — add, modify, and remove rows
Skills taught: sql.insert, sql.update, sql.delete, sql.upsert
INSERT — adding rows
-- Insert a single row
INSERT INTO users (name, email, city, joined_at)
VALUES ('New User', 'new@example.com', 'Pune', '2024-07-01');
-- Insert multiple rows at once
INSERT INTO products (name, category, price, stock)
VALUES
('JavaScript Handbook', 'Books', 449, 300),
('Ergonomic Mouse', 'Electronics', 1499, 60),
('USB Hub', 'Electronics', 899, 90);
-- Insert from a SELECT query
-- (duplicate all book products to an archive table)
-- INSERT INTO archive_products
-- SELECT * FROM products WHERE category = 'Books';
-- Upsert: insert or update on conflict (SQLite)
INSERT INTO users (id, name, email, city, joined_at)
VALUES (1, 'Prashant Updated', 'p2@example.com', 'Delhi', '2024-01-15')
ON CONFLICT(id) DO UPDATE SET
name = excluded.name,
email = excluded.email;
UPDATE and DELETE
-- UPDATE: change specific rows
-- ALWAYS include WHERE unless you want to update ALL rows!
UPDATE products
SET price = 549, stock = 120
WHERE id = 1;
-- Update multiple columns
UPDATE users
SET city = 'Hyderabad', email = 'updated@example.com'
WHERE id = 3;
-- Update based on a calculation
UPDATE products
SET price = ROUND(price * 1.10, 2) -- 10% price increase
WHERE category = 'Books';
-- DELETE: remove specific rows
-- ALWAYS include WHERE!
DELETE FROM orders WHERE id = 8;
-- Delete based on a join condition (using a subquery)
DELETE FROM orders
WHERE user_id = (SELECT id FROM users WHERE email = 'bob@example.com');
-- Check before deleting: run SELECT first!
SELECT * FROM orders WHERE user_id = 3; -- verify
-- Then: DELETE FROM orders WHERE user_id = 3;
Warning: UPDATE and DELETE without a WHERE clause affect ALL rows. Before running either command in production, always run SELECT with the same WHERE condition first to preview affected rows. Wrap destructive operations in a transaction so you can ROLLBACK if something is wrong.
Frequently Asked Questions
How do I get the auto-generated ID after INSERT?
In SQLite: SELECT last_insert_rowid() immediately after INSERT. In PostgreSQL: INSERT INTO table (...) VALUES (...) RETURNING id. In most database drivers (Python's sqlite3, Node's better-sqlite3), the lastInsertRowid is returned directly from the execute call.