INSERT, UPDATE, and DELETE Queries

# CHAPTER 6

INSERT, UPDATE, and DELETE Queries #

1. Introduction #

2. Learning Objectives #

• Use INSERT INTO to add new single or multiple rows.

• Use UPDATE to modify existing data.

• Understand the critical importance of the WHERE clause.

• Use DELETE to safely erase rows.

• Avoid the catastrophic "Global Update/Delete" mistake.

3. Inserting Data (INSERT INTO) #

Syntax:

INSERT INTO table_name (column1, column2, column3) 
VALUES (value1, value2, value3);

Let's insert our first employee into the table we built in Chapter 5. *(Note: Strings and Dates must be wrapped in single quotes ''. Numbers do not).*

INSERT INTO employees (emp_id, first_name, email, salary, hire_date, department) 
VALUES (1, 'John', 'john@company.com', 55000.00, '2024-01-15', 'Sales');

Inserting Multiple Rows at Once: You don't need to write the INSERT INTO command 50 times. You can chain values!

INSERT INTO employees (emp_id, first_name, email, salary, department) 
VALUES 
(2, 'Sarah', 'sarah@company.com', 85000.00, 'IT'),
(3, 'Mike', 'mike@company.com', 45000.00, 'HR'),
(4, 'Anna', 'anna@company.com', 120000.00, 'Exec');

*(Notice we didn't provide a hire_date for these three? If a column allows it, MySQL will simply insert a blank NULL value in that spot).*

4. Updating Data (UPDATE) #

The Golden Rule: You almost *always* must use a WHERE clause to target a specific row. If you forget the WHERE clause, MySQL will update EVERY SINGLE ROW in the entire table!

-- Let's give Sarah (emp_id 2) a massive raise!
UPDATE employees 
SET salary = 95000.00 
WHERE emp_id = 2;

-- We can update multiple columns at once
UPDATE employees 
SET salary = 50000.00, department = 'Sales' 
WHERE emp_id = 3;

5. Deleting Data (DELETE) #

The Golden Rule applies here too: If you forget the WHERE clause, you will wipe out the entire table in a millisecond!

-- Safely delete Mike from the company
DELETE FROM employees 
WHERE emp_id = 3;

-- Delete all employees in the Sales department
DELETE FROM employees 
WHERE department = 'Sales';

6. Mini Project: Manage Employee Records #

-- 1. Hire a new employee
INSERT INTO employees (emp_id, first_name, department) 
VALUES (5, 'David', 'IT');

-- 2. Oops, we forgot his salary. Update his record.
UPDATE employees 
SET salary = 70000.00 
WHERE emp_id = 5;

-- 3. David was fired on day one. Remove him.
DELETE FROM employees 
WHERE emp_id = 5;

7. The Dangers of DML Operations #

8. Common Mistakes #

• String Quote Errors: Typing VALUES (1, John, 55000) will fail because John is text and must be wrapped in quotes: VALUES (1, 'John', 55000).

• Date Format: MySQL strictly requires dates to be inserted as 'YYYY-MM-DD'. If you insert '12-25-2024' or '12/25/24', the insertion will fail.

9. Best Practices #

• Safe Updates: In production environments, phpMyAdmin and modern MySQL interfaces often have "Safe Updates" turned on by default. This feature literally blocks any UPDATE or DELETE command that does not include a WHERE clause targeting a Primary Key, saving you from catastrophic mistakes.

10. Exercises #

1. 1. Write the SQL command to update the department of all employees currently in 'Sales' to a new department named 'Marketing'.

1. 2. What happens if you execute the command UPDATE employees SET salary = 100000;?

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain the difference between DELETE FROM tablename; and DROP TABLE tablename;.

• Q: Describe a scenario where an application might need to execute a bulk INSERT statement containing multiple rows, rather than executing 100 separate INSERT statements.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #