Bài 6: Data Modification

Database Management

Cẩn thận với Production Data

Luôn backup và test trên development database trước khi chạy INSERT/UPDATE/DELETE trên production!

1. INSERT Statement

1.1 Basic INSERT

SQL

1-- INSERT single row với tất cả columns
2INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate)
3VALUES ('Data Science', 'Research and Development', GETDATE());
4
5-- INSERT với specified columns
6INSERT INTO HumanResources.Department (Name, GroupName)
7VALUES ('Analytics', 'Information Technology');
8-- ModifiedDate sẽ dùng default value

1.2 INSERT Multiple Rows

SQL

1INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate)
2VALUES 
3    ('Machine Learning', 'Research and Development', GETDATE()),
4    ('Business Intelligence', 'Information Technology', GETDATE()),
5    ('Data Engineering', 'Information Technology', GETDATE());

1.3 INSERT INTO SELECT

SQL

1-- Copy data từ query result
2INSERT INTO Sales.SalesTerritory_Archive (TerritoryID, Name, CountryRegionCode, [Group])
3SELECT TerritoryID, Name, CountryRegionCode, [Group]
4FROM Sales.SalesTerritory
5WHERE CountryRegionCode = 'US';

1.4 SELECT INTO (Create new table)

SQL

1-- Tạo table mới từ query
2SELECT 
3    p.ProductID,
4    p.Name,
5    p.ListPrice,
6    pc.Name AS Category
7INTO Production.Product_Backup
8FROM Production.Product p
9JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
10JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
11WHERE p.ListPrice > 1000;

1.5 INSERT với OUTPUT

SQL

1-- Capture inserted values
2DECLARE @InsertedDepts TABLE (DeptID INT, DeptName NVARCHAR(50));
3
4INSERT INTO HumanResources.Department (Name, GroupName, ModifiedDate)
5OUTPUT inserted.DepartmentID, inserted.Name INTO @InsertedDepts
6VALUES ('New Department', 'Executive General and Administration', GETDATE());
7
8SELECT * FROM @InsertedDepts;

2. UPDATE Statement

2.1 Basic UPDATE

SQL

1-- UPDATE với WHERE
2UPDATE Production.Product
3SET ListPrice = ListPrice * 1.1
4WHERE ProductID = 1;
5
6-- UPDATE multiple columns
7UPDATE Production.Product
8SET 
9    ListPrice = ListPrice * 1.1,
10    ModifiedDate = GETDATE()
11WHERE ProductSubcategoryID = 1;

NEVER forget WHERE clause!

UPDATE table SET column = value không có WHERE sẽ update TẤT CẢ rows!

2.2 UPDATE với JOIN

SQL

1-- Increase price của Bikes by 5%
2UPDATE p
3SET 
4    p.ListPrice = p.ListPrice * 1.05,
5    p.ModifiedDate = GETDATE()
6FROM Production.Product p
7JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
8JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
9WHERE pc.Name = 'Bikes';

2.3 UPDATE với Subquery

SQL

1-- Update products với price thấp hơn category average
2UPDATE Production.Product
3SET ListPrice = ListPrice * 1.15
4WHERE ListPrice < (
5    SELECT AVG(ListPrice) 
6    FROM Production.Product 
7    WHERE ListPrice > 0
8);

2.4 UPDATE với OUTPUT

SQL

1-- Track changes
2DECLARE @UpdatedProducts TABLE (
3    ProductID INT,
4    OldPrice MONEY,
5    NewPrice MONEY
6);
7
8UPDATE Production.Product
9SET ListPrice = ListPrice * 1.1
10OUTPUT 
11    deleted.ProductID,
12    deleted.ListPrice AS OldPrice,
13    inserted.ListPrice AS NewPrice
14INTO @UpdatedProducts
15WHERE ProductSubcategoryID = 1;
16
17SELECT * FROM @UpdatedProducts;

3. DELETE Statement

3.1 Basic DELETE

SQL

1-- DELETE với WHERE
2DELETE FROM Sales.SalesOrderDetail
3WHERE SalesOrderID = 12345;
4
5-- DELETE với JOIN
6DELETE sod
7FROM Sales.SalesOrderDetail sod
8JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
9WHERE soh.OrderDate < '2011-01-01';

NEVER forget WHERE clause!

DELETE FROM table không có WHERE sẽ xóa TẤT CẢ rows!

3.2 DELETE với Subquery

SQL

1-- Delete products không có sales
2DELETE FROM Production.Product_Archive
3WHERE ProductID NOT IN (
4    SELECT DISTINCT ProductID 
5    FROM Sales.SalesOrderDetail
6);

3.3 DELETE với OUTPUT

SQL

1-- Track deleted records
2DECLARE @DeletedOrders TABLE (
3    OrderID INT,
4    CustomerID INT,
5    OrderDate DATETIME
6);
7
8DELETE FROM Sales.SalesOrderHeader_Archive
9OUTPUT 
10    deleted.SalesOrderID,
11    deleted.CustomerID,
12    deleted.OrderDate
13INTO @DeletedOrders
14WHERE OrderDate < '2011-01-01';
15
16SELECT * FROM @DeletedOrders;

3.4 TRUNCATE vs DELETE

SQL

1-- DELETE: row-by-row, logged, triggers execute, can use WHERE
2DELETE FROM Sales.SalesOrderHeader_Archive;
3
4-- TRUNCATE: deallocate pages, minimal logging, faster, no WHERE
5TRUNCATE TABLE Sales.SalesOrderHeader_Archive;

Feature	DELETE	TRUNCATE
WHERE clause	✅ Yes	❌ No
Triggers	✅ Execute	❌ Don't execute
Transaction log	Full logged	Minimal
Identity reset	❌ No	✅ Yes
Speed	Slower	Faster

4. MERGE Statement

4.1 Concept

MERGE (Upsert) performs INSERT, UPDATE, DELETE in single statement based on source/target comparison.

SQL

1MERGE target_table AS target
2USING source_table AS source
3ON target.key = source.key
4WHEN MATCHED THEN
5    UPDATE SET ...
6WHEN NOT MATCHED BY TARGET THEN
7    INSERT (columns) VALUES (values)
8WHEN NOT MATCHED BY SOURCE THEN
9    DELETE;

4.2 Example: Sync Product Prices

SQL

1-- Create staging table with new prices
2CREATE TABLE #ProductPriceUpdates (
3    ProductID INT,
4    NewListPrice MONEY
5);
6
7INSERT INTO #ProductPriceUpdates VALUES
8    (1, 150.00),
9    (2, 200.00),
10    (999, 50.00);  -- Might not exist
11
12-- MERGE to update
13MERGE Production.Product AS target
14USING #ProductPriceUpdates AS source
15ON target.ProductID = source.ProductID
16WHEN MATCHED THEN
17    UPDATE SET 
18        target.ListPrice = source.NewListPrice,
19        target.ModifiedDate = GETDATE()
20WHEN NOT MATCHED BY TARGET THEN
21    INSERT (Name, ProductNumber, ListPrice, ModifiedDate)
22    VALUES ('New Product', 'NEW-' + CAST(source.ProductID AS VARCHAR), source.NewListPrice, GETDATE());
23
24DROP TABLE #ProductPriceUpdates;

4.3 MERGE với OUTPUT

SQL

1DECLARE @MergeOutput TABLE (
2    Action NVARCHAR(10),
3    ProductID INT,
4    OldPrice MONEY,
5    NewPrice MONEY
6);
7
8MERGE Production.Product AS target
9USING #ProductPriceUpdates AS source
10ON target.ProductID = source.ProductID
11WHEN MATCHED THEN
12    UPDATE SET target.ListPrice = source.NewListPrice
13WHEN NOT MATCHED BY TARGET THEN
14    INSERT (Name, ProductNumber, ListPrice)
15    VALUES ('New', 'NEW-' + CAST(source.ProductID AS VARCHAR), source.NewListPrice)
16OUTPUT 
17    $action,
18    COALESCE(inserted.ProductID, deleted.ProductID),
19    deleted.ListPrice,
20    inserted.ListPrice
21INTO @MergeOutput;
22
23SELECT * FROM @MergeOutput;

5. Transaction Management

5.1 Basic Transaction

SQL

1BEGIN TRANSACTION;
2
3UPDATE Production.Product
4SET ListPrice = ListPrice * 1.1
5WHERE ProductSubcategoryID = 1;
6
7-- Check if update is correct
8IF @@ROWCOUNT > 100
9BEGIN
10    ROLLBACK TRANSACTION;
11    PRINT 'Too many rows affected. Transaction rolled back.';
12END
13ELSE
14BEGIN
15    COMMIT TRANSACTION;
16    PRINT 'Update successful.';
17END

5.2 TRY...CATCH với Transaction

SQL

1BEGIN TRY
2    BEGIN TRANSACTION;
3    
4    -- Update 1
5    UPDATE Production.Product
6    SET ListPrice = ListPrice * 1.1
7    WHERE ProductSubcategoryID = 1;
8    
9    -- Update 2
10    UPDATE Production.Product
11    SET StandardCost = StandardCost * 1.1
12    WHERE ProductSubcategoryID = 1;
13    
14    COMMIT TRANSACTION;
15    PRINT 'All updates successful.';
16END TRY
17BEGIN CATCH
18    IF @@TRANCOUNT > 0
19        ROLLBACK TRANSACTION;
20    
21    PRINT 'Error occurred: ' + ERROR_MESSAGE();
22END CATCH;

5.3 SAVE TRANSACTION

SQL

1BEGIN TRANSACTION;
2
3-- Step 1: Update prices
4UPDATE Production.Product
5SET ListPrice = ListPrice * 1.1
6WHERE ProductSubcategoryID = 1;
7
8SAVE TRANSACTION SavePoint1;
9
10-- Step 2: Update costs
11UPDATE Production.Product
12SET StandardCost = StandardCost * 1.1
13WHERE ProductSubcategoryID = 1;
14
15-- Rollback only step 2 if needed
16IF @@ROWCOUNT > 50
17BEGIN
18    ROLLBACK TRANSACTION SavePoint1;
19    PRINT 'Cost update rolled back.';
20END
21
22COMMIT TRANSACTION;

6. Best Practices

6.1 Always Test First

SQL

1-- Step 1: SELECT to verify rows
2SELECT *
3FROM Production.Product
4WHERE ProductSubcategoryID = 1;
5
6-- Step 2: Run UPDATE only after verifying
7UPDATE Production.Product
8SET ListPrice = ListPrice * 1.1
9WHERE ProductSubcategoryID = 1;

6.2 Use TOP for Batched Updates

SQL

1-- Update in batches of 1000
2WHILE 1 = 1
3BEGIN
4    UPDATE TOP (1000) Production.Product
5    SET ListPrice = ListPrice * 1.1
6    WHERE ListPrice < 100 AND ModifiedDate < '2020-01-01';
7    
8    IF @@ROWCOUNT = 0 BREAK;
9END

6.3 Backup Before Major Changes

SQL

1-- Create backup table
2SELECT * INTO Production.Product_Backup_20240115
3FROM Production.Product;
4
5-- Perform updates
6UPDATE Production.Product
7SET ListPrice = ListPrice * 1.1;
8
9-- If something goes wrong, restore
10-- UPDATE Production.Product
11-- SET ListPrice = (SELECT ListPrice FROM Production.Product_Backup_20240115 b WHERE b.ProductID = Production.Product.ProductID);

7. Thực hành

Exercises

Lưu ý: Tạo backup tables trước khi thực hành!

Exercise 1: INSERT

SQL

1-- Tạo bảng backup và insert departments thuộc 'Manufacturing'
2-- Step 1: CREATE TABLE Department_Backup (DepartmentID, Name, GroupName)
3-- Step 2: INSERT departments WHERE GroupName = 'Manufacturing'
4
5-- YOUR CODE HERE

💡 Xem đáp án

SQL

1-- Create backup table
2SELECT 
3    DepartmentID,
4    Name,
5    GroupName
6INTO HumanResources.Department_Backup
7FROM HumanResources.Department
8WHERE GroupName = 'Manufacturing';
9
10-- Verify
11SELECT * FROM HumanResources.Department_Backup;

Exercise 2: UPDATE với JOIN

SQL

1-- Giảm giá 10% cho tất cả Accessories
2-- Output: ProductID, OldPrice, NewPrice
3
4-- YOUR CODE HERE

💡 Xem đáp án

SQL

1-- First backup
2SELECT ProductID, Name, ListPrice
3INTO Production.Product_Price_Backup
4FROM Production.Product
5WHERE ProductSubcategoryID IN (
6    SELECT ps.ProductSubcategoryID
7    FROM Production.ProductSubcategory ps
8    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
9    WHERE pc.Name = 'Accessories'
10);
11
12-- Update with output
13DECLARE @PriceChanges TABLE (ProductID INT, OldPrice MONEY, NewPrice MONEY);
14
15UPDATE p
16SET p.ListPrice = p.ListPrice * 0.9
17OUTPUT deleted.ProductID, deleted.ListPrice, inserted.ListPrice INTO @PriceChanges
18FROM Production.Product p
19JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
20JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
21WHERE pc.Name = 'Accessories';
22
23SELECT * FROM @PriceChanges;

Exercise 3: DELETE với Subquery

SQL

1-- Xóa records trong backup table với products không có sales
2-- (Sử dụng NOT EXISTS)
3
4-- YOUR CODE HERE

💡 Xem đáp án

SQL

1-- Delete products with no sales from backup
2DELETE FROM Production.Product_Price_Backup
3WHERE NOT EXISTS (
4    SELECT 1 
5    FROM Sales.SalesOrderDetail sod
6    WHERE sod.ProductID = Production.Product_Price_Backup.ProductID
7);

Exercise 4: Transaction

SQL

1-- Viết transaction:
2-- 1. Tăng giá Bikes 5%
3-- 2. Nếu > 100 rows affected, rollback
4-- 3. Print kết quả
5
6-- YOUR CODE HERE

💡 Xem đáp án

SQL

1BEGIN TRY
2    BEGIN TRANSACTION;
3    
4    DECLARE @RowsAffected INT;
5    
6    UPDATE p
7    SET p.ListPrice = p.ListPrice * 1.05
8    FROM Production.Product p
9    JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
10    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
11    WHERE pc.Name = 'Bikes';
12    
13    SET @RowsAffected = @@ROWCOUNT;
14    
15    IF @RowsAffected > 100
16    BEGIN
17        ROLLBACK TRANSACTION;
18        PRINT 'Rolled back. Too many rows: ' + CAST(@RowsAffected AS VARCHAR);
19    END
20    ELSE
21    BEGIN
22        COMMIT TRANSACTION;
23        PRINT 'Success. Updated ' + CAST(@RowsAffected AS VARCHAR) + ' rows.';
24    END
25END TRY
26BEGIN CATCH
27    IF @@TRANCOUNT > 0
28        ROLLBACK TRANSACTION;
29    PRINT 'Error: ' + ERROR_MESSAGE();
30END CATCH;

8. Tổng kết

Statement	Purpose	Key Points
INSERT	Add new rows	Use OUTPUT to capture inserted IDs
UPDATE	Modify existing rows	Always use WHERE!
DELETE	Remove rows	Test with SELECT first!
MERGE	Upsert (sync)	Combine INSERT/UPDATE/DELETE
TRUNCATE	Fast delete all	Resets identity, minimal logging

Safety Checklist:

☑️ Backup important tables
☑️ Test với SELECT trước
☑️ Use transactions cho multiple operations
☑️ Check @@ROWCOUNT sau mỗi operation
☑️ Use OUTPUT để track changes

Bài tiếp theo: String và DateTime Functions