Understanding Data Manipulation Language(DML) statements in SQL

Data Manipulation Language (DML) statements are those SQL statements we use to insert, update, or delete data in tables (e.g., INSERT, UPDATE, DELETE).

In this article, I am going to show you how to use the INSERT, UPDATE, and DELETE statements in SQL, including use cases, examples, and professional information to aid in learning and provide practical examples.

INSERT Statement

The INSERT statement is used to add new rows of data into a table. It is a fundamental operation in SQL and is part of the Data Manipulation Language (DML) subset.

Use Cases:

• Adding new records to a table (e.g., new customers, orders, products)
• Populating a newly created table with initial data
• Inserting data from one table into another (combined with SELECT)

Syntax:

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

Examples:

1. Insert a new row into the "Customers" table:

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('John Doe', 'New York', 'USA');

1. Insert multiple rows into the "Orders" table:

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES
    (1001, 101, '2023-05-01'),
    (1002, 102, '2023-05-02'),
    (1003, 103, '2023-05-03');

1. Insert data from a SELECT query (using a subquery):

INSERT INTO BackupCustomers
SELECT * FROM Customers;

Professional Tips:

• Use column lists to specify the columns you want to insert data into, ensuring the values match the column order.
• Be cautious when omitting columns with default values or auto-incrementing keys, as the DBMS may handle them differently.
• Consider using transactions when inserting multiple rows to ensure data integrity in case of errors or failures.
• Optimize INSERT statements by batching or bulk inserting data instead of individual inserts for better performance.

UPDATE Statement

The UPDATE statement is used to modify existing data in a table. It allows you to change the values of one or more columns for selected rows that match a specified condition.

Use Cases:

• Updating customer information (e.g., address, phone number)
• Modifying product prices or stock quantities
• Correcting data entry errors or inconsistencies

Syntax:

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

Examples:

1. Update the email address for a specific customer:

UPDATE Customers
SET Email = '[e-mail]'
WHERE CustomerID = 101;

1. Update multiple columns with different values:

UPDATE Products
SET Price = Price * 1.1, StockQuantity = StockQuantity - 10
WHERE ProductID IN (101, 102, 103);

1. Update rows based on a subquery:

UPDATE Orders
SET ShipDate = CURRENT_DATE()
WHERE OrderID IN (
    SELECT OrderID
    FROM Orders
    WHERE ShipDate IS NULL
);

Professional Tips:

• Always include a WHERE clause to specify the rows you want to update; omitting it will update all rows in the table.
• Use transactions when updating critical data to ensure data integrity and rollback capability in case of errors.
• Consider indexing the columns used in the WHERE clause for better performance on large tables.
• Test your UPDATE statements thoroughly, especially when updating multiple columns or using subqueries, to avoid unintended data modifications.

DELETE Statement

The DELETE statement is used to remove existing rows from a table. It allows you to delete specific rows that match a specified condition or all rows from a table.

Use Cases:

• Removing obsolete or unnecessary data (e.g., old customer records, expired products)
• Deleting test data or temporary records
• Truncating a table by deleting all rows

Syntax:

DELETE FROM table_name
WHERE condition;

Examples:

1. Delete a specific customer record:

DELETE FROM Customers
WHERE CustomerID = 101;

1. Delete all orders for a specific customer:

DELETE FROM Orders
WHERE CustomerID = (
    SELECT CustomerID
    FROM Customers
    WHERE CustomerName = 'John Doe'
);

1. Delete all rows from a table (truncate):

DELETE FROM TemporaryTable;

Professional Tips:

• Use the WHERE clause to specify the rows you want to delete; omitting it will delete all rows in the table.
• Be extremely cautious when deleting data, as it is a permanent operation (unless using transactions or backups).
• Consider using transactions when deleting critical data to ensure data integrity and rollback capability in case of errors.
• Truncating a table (deleting all rows) can be faster than deleting rows one by one, but it cannot be rolled back and may cause issues with identity columns or triggers.
• Indexing the columns used in the WHERE clause can improve performance for large tables.

Practical Examples:

1. Customer Management System:

   • INSERT new customers into the "Customers" table when they sign up.
   • UPDATE customer information (address, phone number) when they change their details.
   • DELETE customer records when they request to close their accounts.

2. E-commerce Order Processing:

   • INSERT new orders into the "Orders" table when customers place an order.
   • UPDATE order status (e.g., "Shipped") when the order is processed.
   • DELETE order records after a certain period (e.g., 2 years) for archiving purposes.

3. Inventory Management:

   • INSERT new products into the "Products" table when new items are added to the inventory.
   • UPDATE product prices or stock quantities based on supplier changes or sales.
   • DELETE product records when items are discontinued or removed from the inventory.

4. Data Warehousing and Analytics:

   • INSERT new data into staging tables or data marts for analysis.
   • UPDATE dimension tables with slowly changing dimensions (e.g., customer addresses, product categories).
   • DELETE outdated or obsolete data from fact tables or data marts during data refreshes.

Wrapping Up

In this lesson, we covered three essential SQL statements: INSERT, UPDATE, and DELETE, which are part of the Data Manipulation Language (DML) subset. These statements allow us to manage data in databases by adding, modifying, and removing rows from tables.

The INSERT statement is used to add new rows of data into a table. We learned its syntax, common use cases, and best practices, such as using column lists, considering default values and auto-incrementing keys, batching inserts for better performance, and using transactions for data integrity.

The UPDATE statement is used to modify existing data in a table by changing the values of one or more columns for selected rows that match a specified condition. We discussed its syntax, use cases like updating customer information or product prices, and tips like always including a WHERE clause, using transactions for critical data, indexing columns used in the WHERE clause, and thoroughly testing updates before execution.

The DELETE statement is used to remove existing rows from a table based on a specified condition or all rows. We covered its syntax, use cases such as removing obsolete data or truncating tables, and tips like using the WHERE clause to specify rows to delete, being cautious as deletion is permanent, considering transactions, and indexing columns used in the WHERE clause.

Throughout the lesson, we explored practical examples demonstrating the application of these statements in scenarios like customer management systems, e-commerce order processing, inventory management, and data warehousing and analytics.

Mastering these fundamental SQL statements is essential for effective data manipulation and maintenance in various applications and scenarios. By understanding their syntax, use cases, and best practices, you can confidently manage data in databases, ensuring data integrity, consistency, and accuracy.