SQL CRUD Operations Complete Guide: INSERT, UPDATE, DELETE Syntax Tutorial [2025]

CRUD represents the four basic database operations: Create, Read, Update, Delete. Read uses SELECT statements (detailed in other articles), while Create, Update, Delete correspond to SQL's INSERT, UPDATE, DELETE commands respectively.

These three commands belong to DML (Data Manipulation Language) and are fundamental skills every developer must master. This article systematically introduces the syntax structure, advanced usage, and security considerations for these commands, helping you operate database data correctly and safely.

---

CRUD Operations Overview

Before learning each command, understand the common characteristics of DML commands:

DML Command Characteristics

Characteristic	Description
Rollbackable	Can be rolled back when executed within a Transaction
Trigger Firing	May fire triggers defined on the table
Log Generation	Every operation is recorded in the transaction log
Resource Locking	Locks relevant rows or pages during execution

Safety Recommendations Before Operations

Important Principle: Before modifying data, always confirm the scope of impact!

-- ❌ Dangerous: Execute UPDATE/DELETE directly
DELETE FROM Orders WHERE Status = 'Cancelled';

-- ✅ Safe: First use SELECT to confirm scope
SELECT * FROM Orders WHERE Status = 'Cancelled';
-- After confirming data is correct, execute DELETE

---

INSERT Adding Data

INSERT statements are used to add one or more records to a table.

Basic Syntax

-- Syntax 1: Specify column names (recommended)
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Syntax 2: Omit column names (must follow table column order)
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Single Record Insert Example

-- Insert one employee record
INSERT INTO Employees (EmployeeID, Name, Email, DepartmentID, HireDate)
VALUES (101, 'Alice Chen', '[email protected]', 1, '2025-01-15');

-- Use DEFAULT keyword to insert default value
INSERT INTO Employees (EmployeeID, Name, Email, DepartmentID, HireDate)
VALUES (102, 'Bob Wang', '[email protected]', DEFAULT, GETDATE());

Batch Insert (Multiple Records)

Inserting multiple records at once is much more performant than inserting one at a time:

-- Batch insert multiple records
INSERT INTO Products (ProductName, Price, CategoryID)
VALUES
    ('Product A', 100, 1),
    ('Product B', 200, 1),
    ('Product C', 150, 2),
    ('Product D', 300, 2),
    ('Product E', 250, 3);

Performance Comparison:

Method	10,000 Records	Description
Individual INSERT	~30 seconds	Each is a separate transaction
Batch INSERT	~1 second	Single transaction processes multiple records

INSERT INTO SELECT

Import data from other tables or query results:

-- Insert data from query results
INSERT INTO ArchivedOrders (OrderID, CustomerID, OrderDate, TotalAmount)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate < '2024-01-01';

-- With condition filtering
INSERT INTO VIPCustomers (CustomerID, CustomerName, TotalSpent)
SELECT CustomerID, CustomerName, SUM(Amount)
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName
HAVING SUM(Amount) > 100000;

Handling Auto-Increment Columns

When a table has auto-increment (IDENTITY / AUTO_INCREMENT) columns:

-- SQL Server: Get newly inserted ID
INSERT INTO Employees (Name, Email)
VALUES ('Carol Lee', '[email protected]');

SELECT SCOPE_IDENTITY() AS NewEmployeeID;

-- MySQL: Get newly inserted ID
INSERT INTO Employees (Name, Email)
VALUES ('Carol Lee', '[email protected]');

SELECT LAST_INSERT_ID() AS NewEmployeeID;

INSERT Common Errors

Error Type	Cause	Solution
Primary Key Duplicate	Inserting existing primary key value	Check primary key value or use UPSERT
Foreign Key Violation	Referenced foreign key value doesn't exist	Ensure parent table has corresponding data first
NOT NULL Violation	Required column not provided	Provide value or set default value
Data Type Mismatch	Value doesn't match column type	Use CAST/CONVERT to convert

---

UPDATE Modifying Data

UPDATE statements are used to modify existing data in a table.

Basic Syntax

UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2
WHERE condition;

Warning: UPDATE without WHERE condition will update all data in the table!

Basic Update Examples

-- Update single column
UPDATE Employees
SET Email = '[email protected]'
WHERE EmployeeID = 101;

-- Update multiple columns
UPDATE Employees
SET
    DepartmentID = 2,
    Salary = Salary * 1.1,  -- 10% raise
    UpdatedAt = GETDATE()
WHERE EmployeeID = 101;

Conditional Updates

Precise updates with various conditions:

-- Using IN condition
UPDATE Products
SET IsActive = 0
WHERE CategoryID IN (5, 6, 7);

-- Using BETWEEN condition
UPDATE Orders
SET Discount = 0.1
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

-- Using LIKE condition
UPDATE Customers
SET CustomerType = 'Enterprise'
WHERE CompanyName LIKE '%Corp%' OR CompanyName LIKE '%Inc%';

Updates with Expressions

-- Numeric operations
UPDATE Products
SET Price = Price * 0.9  -- 10% off
WHERE CategoryID = 1;

-- String processing
UPDATE Customers
SET Email = LOWER(Email);  -- Convert Email to lowercase

-- Conditional operations (CASE WHEN)
UPDATE Employees
SET Bonus = CASE
    WHEN Performance >= 90 THEN Salary * 0.2
    WHEN Performance >= 80 THEN Salary * 0.1
    ELSE Salary * 0.05
END;

JOIN UPDATE (Cross-Table Update)

Update based on data from other tables:

-- SQL Server syntax
UPDATE e
SET e.DepartmentName = d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- MySQL syntax
UPDATE Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
SET e.DepartmentName = d.DepartmentName;

-- Standard SQL (using subquery)
UPDATE Employees
SET DepartmentName = (
    SELECT DepartmentName
    FROM Departments
    WHERE Departments.DepartmentID = Employees.DepartmentID
)
WHERE DepartmentID IS NOT NULL;

UPDATE Safety Practices

-- Step 1: First use SELECT to confirm scope
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE DepartmentID = 1 AND HireDate < '2024-01-01';
-- Result: 5 rows

-- Step 2: After confirming, execute UPDATE
UPDATE Employees
SET Salary = Salary * 1.05
WHERE DepartmentID = 1 AND HireDate < '2024-01-01';
-- Message: 5 rows affected (matches expectation)

---

DELETE Removing Data

DELETE statements are used to remove data from a table.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Warning: DELETE without WHERE condition will delete all data in the table!

Basic Delete Examples

-- Delete single record
DELETE FROM Employees
WHERE EmployeeID = 101;

-- Delete multiple records matching condition
DELETE FROM Orders
WHERE OrderDate < '2020-01-01' AND Status = 'Completed';

-- Delete using IN condition
DELETE FROM Products
WHERE ProductID IN (1001, 1002, 1003);

Delete with Subquery

-- Delete customers without orders
DELETE FROM Customers
WHERE CustomerID NOT IN (
    SELECT DISTINCT CustomerID FROM Orders
);

-- Using EXISTS (better performance)
DELETE FROM Customers c
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);

JOIN DELETE (Cross-Table Delete)

-- SQL Server: Delete all employees from specific department
DELETE e
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Dissolved Department';

-- MySQL syntax
DELETE e
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Dissolved Department';

DELETE vs TRUNCATE

Feature	DELETE	TRUNCATE
Syntax	DELETE FROM table WHERE condition	TRUNCATE TABLE table
WHERE Condition	Supported	Not supported (deletes all)
Execution Speed	Slower (row by row)	Very fast (resets table)
Transaction Log	Records each row deletion	Minimal logging
Rollbackable	Yes (within transaction)	Depends on database
Trigger Firing	Yes	No
Reset IDENTITY	No	Yes (restarts from 1)
Foreign Key Constraint	Executes (if no violation)	Cannot execute with foreign key references

-- DELETE: Delete all data but keep IDENTITY counter
DELETE FROM TempData;

-- TRUNCATE: Clear table and reset IDENTITY
TRUNCATE TABLE TempData;

Safe Delete Strategy

Soft Delete: Don't actually delete data, mark as deleted instead

-- Table design: Add IsDeleted column
ALTER TABLE Customers ADD IsDeleted BIT DEFAULT 0;
ALTER TABLE Customers ADD DeletedAt DATETIME NULL;

-- Soft delete operation
UPDATE Customers
SET IsDeleted = 1, DeletedAt = GETDATE()
WHERE CustomerID = 1001;

-- Exclude deleted data when querying
SELECT * FROM Customers WHERE IsDeleted = 0;

---

Transaction Control for Data Integrity

When executing INSERT, UPDATE, DELETE, use Transactions to ensure data integrity.

Basic Transaction Syntax

-- Begin transaction
BEGIN TRANSACTION;

-- Execute operations
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 'A001';
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 'A002';

-- Commit after confirming
COMMIT TRANSACTION;

-- Or rollback on error
-- ROLLBACK TRANSACTION;

With Error Handling

BEGIN TRANSACTION;

BEGIN TRY
    -- Debit
    UPDATE Accounts
    SET Balance = Balance - 1000
    WHERE AccountID = 'A001';

    -- Check if balance is sufficient
    IF (SELECT Balance FROM Accounts WHERE AccountID = 'A001') < 0
        THROW 50001, 'Insufficient balance', 1;

    -- Credit
    UPDATE Accounts
    SET Balance = Balance + 1000
    WHERE AccountID = 'A002';

    COMMIT TRANSACTION;
    PRINT 'Transfer successful';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Transfer failed: ' + ERROR_MESSAGE();
END CATCH;

Batch Processing for Large Data Updates

When updating large amounts of data, batch processing is recommended to avoid long-term locking:

DECLARE @BatchSize INT = 1000;
DECLARE @RowsAffected INT = 1;

WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET Status = 'Archived'
    WHERE OrderDate < '2020-01-01' AND Status = 'Completed';

    SET @RowsAffected = @@ROWCOUNT;

    -- Optional: Add delay to avoid resource contention
    WAITFOR DELAY '00:00:01';
END

---

Common Errors and Considerations

1. Forgetting WHERE Condition

-- ❌ Catastrophic error: Update all data
UPDATE Employees SET Salary = 50000;

-- ❌ Catastrophic error: Delete all data
DELETE FROM Orders;

-- ✅ Correct: Add WHERE condition
UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 101;

2. Foreign Key Constraint Violation

-- Error: Deleting parent record with child records
DELETE FROM Departments WHERE DepartmentID = 1;
-- Error message: The DELETE statement conflicted with the REFERENCE constraint

-- Solution 1: Delete child records first
DELETE FROM Employees WHERE DepartmentID = 1;
DELETE FROM Departments WHERE DepartmentID = 1;

-- Solution 2: Use CASCADE DELETE (needs to be set in table definition)

3. Data Type Mismatch

-- Error: Comparing string with number
DELETE FROM Products WHERE ProductID = '100';  -- ProductID is INT

-- Correct: Use correct data type
DELETE FROM Products WHERE ProductID = 100;

4. NULL Value Handling

-- Error: NULL cannot be compared with =
UPDATE Employees SET ManagerID = 1 WHERE ManagerID = NULL;

-- Correct: Use IS NULL
UPDATE Employees SET ManagerID = 1 WHERE ManagerID IS NULL;

---

FAQ

Q1: What's the difference between DELETE and TRUNCATE?

DELETE is a DML statement that can use WHERE conditions to delete specific data, records each deletion in the transaction log, can be rolled back within a transaction, and fires DELETE Triggers. TRUNCATE is a DDL statement that can only delete all data in a table (no WHERE support), executes very fast because it essentially "resets the table" rather than deleting row by row, minimal logging, and resets the IDENTITY column counter. Selection recommendation: Use DELETE for conditional deletion or when audit trail is needed; use TRUNCATE to clear entire table when rollback isn't needed.

Q2: How to safely update large amounts of data?

Safe steps for updating large data: (1) Confirm with SELECT first: Apply UPDATE's WHERE condition to SELECT, confirm the number and content of affected rows; (2) Use transactions: Wrap UPDATE in BEGIN TRANSACTION, COMMIT after confirming results, ROLLBACK if there are issues; (3) Batch processing: Use UPDATE TOP (N) with loops for large updates to avoid long-term table locking affecting other users; (4) Backup data: Backup relevant data before important updates, or use OUTPUT clause to record before/after values; (5) Execute during off-peak hours: Schedule large updates during low system load periods.

---

Further Learning Resources

For deeper SQL data manipulation skills, reference these resources:

SQL Basic Syntax:

• SQL Complete Guide: Database Query Language Tutorial from Beginner to Expert
• SQL Syntax Complete Guide: SELECT, JOIN, WHERE Query Commands Tutorial
• SQL JOIN Complete Tutorial: INNER, LEFT, RIGHT, FULL JOIN Illustrated
• SQL Functions Complete Guide: String, Date, Math, Aggregate Functions Summary

Advanced Techniques:

• SQL Advanced Techniques: Stored Procedures, Triggers, Transaction Control Practical Tutorial
• SQL Performance Tuning Complete Guide: Query Optimization and Index Strategy
• SQL Injection Complete Attack and Defense Guide: Principle Analysis and Prevention

---

Conclusion

INSERT, UPDATE, DELETE are the three core commands for SQL data manipulation. Mastering these syntax is fundamental to database development. Key takeaways:

• INSERT: Add data, leverage batch insert for performance
• UPDATE: Modify data, always add WHERE condition
• DELETE: Remove data, always add WHERE condition, understand difference with TRUNCATE
• Transaction Control: Use Transaction for important operations to ensure data integrity
• Safety Principle: Use SELECT to confirm scope before execution

Remember: Data operation errors are often irreversible. Developing the habit of "confirm before execute" will help you avoid many unnecessary troubles.

---

Want quick reference for CRUD syntax? — Download our organized SQL CRUD syntax template, including INSERT, UPDATE, DELETE common syntax and examples for daily development reference.

Free Download SQL CRUD Syntax Template →

---

References

• Microsoft INSERT Documentation - https://docs.microsoft.com/sql/t-sql/statements/insert-transact-sql
• Microsoft UPDATE Documentation - https://docs.microsoft.com/sql/t-sql/queries/update-transact-sql
• Microsoft DELETE Documentation - https://docs.microsoft.com/sql/t-sql/statements/delete-transact-sql
• MySQL DML Statements - https://dev.mysql.com/doc/refman/8.0/en/sql-data-manipulation-statements.html
• PostgreSQL DML Commands - https://www.postgresql.org/docs/current/dml.html