Play Store Application link – SQL in 18 steps – App on Google Play
Introduction to DML
DML (Data Manipulation Language) allows us to insert, update, delete, and merge data within database tables. Think of it like adding, modifying, or removing items from a shopping list.
1. INSERT INTO – Adding Data to Tables
Single Row Insert
Example: Adding a new student to the Students table.
INSERT INTO Students (student_id, name, age, email)
VALUES (1, 'John Doe', 20, 'john.doe@example.com');
Multiple Rows Insert
Adding multiple students in a single query:
INSERT INTO Students (student_id, name, age, email) VALUES
(2, 'Jane Smith', 22, 'jane.smith@example.com'),
(3, 'Alice Brown', 21, 'alice.brown@example.com');
Differences in INSERT Syntax Across Databases
| Feature | MySQL Syntax | Oracle Syntax |
|---|---|---|
| Multiple Rows | Supported | Supported (from 12c) |
| Auto-increment | AUTO_INCREMENT | GENERATED AS IDENTITY |
| Returning Inserted ID | SELECT LAST_INSERT_ID(); | RETURNING id INTO var; |
2. UPDATE – Modifying Existing Data
Updating a Single Column
Updating the email of a student:
UPDATE Students
SET email = 'john.new@example.com'
WHERE student_id = 1;
Updating Multiple Columns
Changing the name and age of a student:
UPDATE Students
SET name = 'John Updated', age = 21
WHERE student_id = 1;
Updating Multiple Rows
Increasing the age of all students by 1:
UPDATE Students
SET age = age + 1;
3. DELETE – Removing Data
Deleting a Specific Record
DELETE FROM Students WHERE student_id = 2;
Deleting All Records (Use with caution!)
DELETE FROM Students;
DELETE vs TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes Specific Rows | ✅ | ❌ |
| Removes All Rows | ✅ | ✅ |
| Can Be Rolled Back | ✅ | ❌ |
| Resets Auto-Increment | ❌ | ✅ |
4. MERGE – Upsert (Update + Insert) Data
MERGE is useful when we need to insert new data if it doesn’t exist, or update it if it does.
Example: Merging Student Data (Oracle & SQL Server)
MERGE INTO Students s
USING (SELECT 1 AS student_id, 'John Doe' AS name, 20 AS age FROM dual) src
ON (s.student_id = src.student_id)
WHEN MATCHED THEN
UPDATE SET s.name = src.name, s.age = src.age
WHEN NOT MATCHED THEN
INSERT (student_id, name, age) VALUES (src.student_id, src.name, src.age);
Conclusion
DML commands help in managing data inside tables. The key operations include inserting, updating, and deleting data, while MERGE allows efficient upserting.