Rahul writes, updates and deletes safely
INSERT, UPDATE, DELETE, ALTER TABLE — changing data without destroying it
Rahul's app was live. Users registering, placing orders, updating profiles, cancelling orders. He needed to write, update, and delete data -- not just read it.
The golden rule before every change: SELECT first to confirm what will be affected, then modify.
INSERT -- ADDING NEW DATA
INSERT INTO users (name, email, city)
VALUES ('Deepa Nair', 'deepa@example.com', 'Kochi');-- Multiple rows at once (faster):
INSERT INTO users (name, email, city) VALUES
('Sanjay Gupta', 'sanjay@example.com', 'Pune'),
('Kavita Reddy', 'kavita@example.com', 'Hyderabad');-- Skip if duplicate key exists:
INSERT IGNORE INTO users (name, email, city)
VALUES ('Rahul Sharma', 'rahul@example.com', 'Hyderabad');-- Insert or update if exists (upsert):
INSERT INTO users (id, name, email, city)
VALUES (1, 'Rahul Sharma', 'rahul@example.com', 'Pune')
ON DUPLICATE KEY UPDATE city = 'Pune';UPDATE -- CHANGING DATA
CRITICAL RULE: Always write the SELECT with the same WHERE clause first.
-- First, check what you are about to change:
SELECT * FROM users WHERE id = 3;-- Then update:
UPDATE users SET city = 'Bangalore' WHERE id = 3;-- Update multiple columns:
UPDATE users SET city = 'Bangalore', name = 'Arjun K' WHERE id = 3;THE DISASTER OF FORGETTING WHERE
-- This updates EVERY row:
UPDATE users SET city = 'Mumbai';
-- All 50,000 users now show city = Mumbai. This cannot be easily undone.Enable safe mode to protect yourself:
SET SQL_SAFE_UPDATES = 1;
-- Now MySQL refuses UPDATE and DELETE without a WHERE using a primary keyDELETE -- REMOVING DATA
-- Check first:
SELECT * FROM users WHERE id = 99;
-- Then delete:
DELETE FROM users WHERE id = 99;-- Count before deleting old records:
SELECT COUNT(*) FROM orders WHERE status = 'cancelled' AND ordered_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Then delete:
DELETE FROM orders WHERE status = 'cancelled' AND ordered_at < DATE_SUB(NOW(), INTERVAL 30 DAY);SOFT DELETE -- THE PROFESSIONAL APPROACH
In production, permanently deleting data is risky. Most professional systems use soft delete: mark as deleted instead of removing.
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;-- Soft delete:
UPDATE users SET deleted_at = NOW() WHERE id = 99;-- Query only active users:
SELECT * FROM users WHERE deleted_at IS NULL;-- Restore a soft-deleted user:
UPDATE users SET deleted_at = NULL WHERE id = 99;ALTER TABLE -- CHANGING STRUCTURE
ALTER TABLE users ADD COLUMN phone VARCHAR(15) AFTER email;
ALTER TABLE users ADD COLUMN is_verified TINYINT(1) DEFAULT 0;
ALTER TABLE users MODIFY COLUMN city VARCHAR(100);
ALTER TABLE users DROP COLUMN old_column;Rahul added a phone column, updated existing users, and soft-deleted closed accounts. He never lost data permanently.
Always SELECT with the same WHERE clause before running UPDATE or DELETE — confirm exactly which rows will change
UPDATE without WHERE changes EVERY row in the table — this single mistake has caused countless production disasters
Soft delete (deleted_at column) is safer than DELETE — you can always restore, you cannot undelete
INSERT IGNORE skips duplicates silently, ON DUPLICATE KEY UPDATE upserts — updates if the row exists, inserts if not
SET SQL_SAFE_UPDATES = 1 makes MySQL reject UPDATE and DELETE without a WHERE clause — use in development always