21. Modifying Data: INSERT, UPDATE, DELETE

Core Idea: Data Modification Language (DML) statements alter database state. INSERT inserts new rows, UPDATE alters existing column values, and DELETE removes rows.

INSERT Statement

            INSERT INTO Products (ProductName, CategoryID, UnitPrice, UnitsInStock)
            VALUES ('Wireless Mouse', 3, 24.99, 150);
        

UPDATE Statement

Always ensure an accurate WHERE clause exists when issuing updates to avoid modifying every row in the table!

            UPDATE Products 
            SET UnitPrice = UnitPrice * 1.05,
                LastModifiedDate = GETDATE()
            WHERE CategoryID = 3 AND UnitsInStock > 0;
        

DELETE Statement

            DELETE FROM Products 
            WHERE Discontinued = 1 AND UnitsInStock = 0;
        

Advanced Deep Dive ADVANCED

Transactions (ACID Safety): Always wrap multi-step data mutations in explicit transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) to guarantee atomicity and rollback capability on error.

OUTPUT Clause: Use OUTPUT inserted.*, deleted.* to capture created or modified records for auditing or immediate application consumption without a secondary query.

MERGE Statement: Performs conditional INSERT, UPDATE, or DELETE operations in a single atomic statement based on source/target synchronization.

Constraints & Foreign Keys: Check constraints, foreign key referential integrity, and triggers ensure invalid or orphaned data mutations are rejected automatically.