DML (Data Manipulation Language) Commands in SQL

DML (Data Manipulation Language) consists of SQL commands used to manipulate data within tables. Unlike DDL, which changes the database structure, DML is used to perform operations on the data itself.

Main DML Commands:

1. INSERT – Adds new records into a table.
2. UPDATE – Modifies existing records in a table.
3. DELETE – Removes records from a table.
4. MERGE – Combines INSERT, UPDATE, and DELETE operations into a single statement (used in advanced SQL).

---

1. INSERT Command

The INSERT command is used to add new rows (records) into a table.

Syntax:

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

Example:

INSERT INTO Students (StudentID, Name, Age, Course) 
VALUES (101, 'John Doe', 20, 'Computer Science');

Here, a new student record is added to the Students table.

Inserting Multiple Rows:

INSERT INTO Students (StudentID, Name, Age, Course) 
VALUES 
(102, 'Jane Smith', 22, 'Mathematics'),
(103, 'Mike Johnson', 21, 'Physics');

This adds two new student records in a single query.

Inserting Data Without Specifying Column Names:

INSERT INTO Students 
VALUES (104, 'Emma Watson', 19, 'Biology');

⚠ Caution: If column names are not specified, values must be provided in the exact order of the table schema.

---

2. UPDATE Command

The UPDATE command is used to modify existing records in a table.

Syntax:

UPDATE table_name 
SET column1 = value1, column2 = value2, ... 
WHERE condition;

Example:

UPDATE Students 
SET Age = 21 
WHERE StudentID = 101;

This updates the Age of the student with StudentID = 101.

Updating Multiple Columns:

UPDATE Students 
SET Age = 22, Course = 'Data Science' 
WHERE StudentID = 102;

Here, both Age and Course are updated for StudentID = 102.

⚠ Warning: If the WHERE clause is omitted, all records in the table will be updated.
Example:

UPDATE Students 
SET Age = 23;

This will update the Age of all students to 23, which may not be the desired outcome.

---

3. DELETE Command

The DELETE command is used to remove records from a table.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Students 
WHERE StudentID = 103;

This removes the student record with StudentID = 103.

Deleting All Records from a Table:

DELETE FROM Students;

This removes all records from the Students table but keeps the table structure intact.

Difference Between DELETE and TRUNCATE:

• DELETE removes specific rows and can be rolled back using ROLLBACK.
• TRUNCATE removes all rows but cannot be rolled back.

---

4. MERGE Command (Advanced SQL)

The MERGE command is used to perform INSERT, UPDATE, and DELETE operations based on certain conditions. It is useful when combining data from two tables.

Syntax:

MERGE INTO target_table USING source_table
ON (condition)
WHEN MATCHED THEN 
    UPDATE SET column1 = value1, column2 = value2
WHEN NOT MATCHED THEN 
    INSERT (column1, column2) VALUES (value1, value2);

Example:

MERGE INTO Students AS target
USING NewStudents AS source
ON target.StudentID = source.StudentID
WHEN MATCHED THEN 
    UPDATE SET target.Age = source.Age
WHEN NOT MATCHED THEN 
    INSERT (StudentID, Name, Age, Course) 
    VALUES (source.StudentID, source.Name, source.Age, source.Course);

Here, the MERGE statement:

• Updates the Age if the student already exists in the Students table.
• Inserts new student records if they do not exist.

---

Summary of DML Commands:

Command	Purpose
INSERT	Adds new records into a table.
UPDATE	Modifies existing records in a table.
DELETE	Removes records from a table.
MERGE	Combines INSERT, UPDATE, and DELETE in a single command (Advanced SQL).

DML commands work with data and can be combined with transaction control commands like COMMIT, ROLLBACK, and SAVEPOINT to ensure data integrity.