Category: SQL

The UPDATE statement is one of the most frequently used SQL commands.

It’s also one of the most dangerous.

A single mistake can modify thousands—or even millions—of rows in seconds.

That’s why every developer should understand not just how to use UPDATE, but how to use it safely.

Basic Syntax

UPDATE Employees
SET Salary = 60000
WHERE EmployeeId = 101;

This updates only the employee with ID 101.

---

The Most Dangerous Mistake

Imagine running this query:

UPDATE Employees
SET Salary = 60000;

Notice what’s missing?

The WHERE clause.

This statement updates every row in the table.

In production, this mistake can be disastrous.

---

Always Verify Before Updating

Before executing an UPDATE, run the same condition with a SELECT.

Instead of this:

UPDATE Employees
SET Salary = 60000
WHERE Department = 'HR';

Verify the affected rows first:

SELECT *
FROM Employees
WHERE Department = 'HR';

If the SELECT returns the expected rows, you’re much safer executing the update.

---

Use Transactions for Large Updates

For critical changes, wrap your update in a transaction.

BEGIN TRANSACTION;

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Department = 'HR';

-- Verify the results

COMMIT;

-- Or rollback if something looks wrong
-- ROLLBACK;

Transactions give you a chance to validate the changes before making them permanent.

---

Update Only What You Need

Avoid unnecessary updates.

Instead of updating every column:

UPDATE Employees
SET
    Name = 'John',
    Salary = 60000,
    Department = 'IT';

Update only the columns that actually changed.

This reduces unnecessary writes and can improve performance.

---

Be Careful with Joins

SQL Server allows updates using joins.

Example:

UPDATE e
SET e.ManagerId = d.ManagerId
FROM Employees e
JOIN Departments d
ON e.DepartmentId = d.Id;

Always verify the join with a SELECT first to ensure it returns the expected rows.

---

Performance Tips

✔ Ensure the WHERE column is indexed.

✔ Avoid updating millions of rows in a single transaction when possible.

✔ Batch large updates to reduce locking and transaction log growth.

✔ Review the execution plan for expensive updates.

---

Best Practices Checklist

• ✅ Always use a WHERE clause unless you intentionally want to update every row.
• ✅ Run a SELECT first to verify the affected rows.
• ✅ Use transactions for critical updates.
• ✅ Update only the required columns.
• ✅ Test large updates in a non-production environment first.

Final Thoughts

Writing an UPDATE statement is easy.

Writing a safe UPDATE statement is what separates experienced developers from beginners.

A few extra seconds spent verifying your query can prevent hours of recovery work later.

When querying data in SQL, two operators are commonly used to compare values:

• =
• LIKE

Although they may seem interchangeable in some cases, they serve different purposes and can have very different performance characteristics.

Understanding when to use each operator helps you write more accurate and efficient queries.

The ‘=’ Operator

The = operator is used for an exact match.

Example:

SELECT *
FROM Employees
WHERE FirstName = 'John';

This query returns only rows where the FirstName is exactly John.

It won’t match:

• Johnny
• Johnson
• John Doe

Only the exact value.

When to Use ‘=’

Use = when:

• Searching for an exact value
• Comparing IDs
• Matching usernames
• Filtering status values
• Looking up primary or unique keys

---

The LIKE Operator

LIKE is used for pattern matching.

Example:

SELECT *
FROM Employees
WHERE FirstName LIKE 'John%';

This query matches:

• John
• Johnny
• Johnson

because % means zero or more characters.

---

Common Wildcards

Starts With

WHERE FirstName LIKE 'Jo%'

Matches:

• John
• Joseph
• Jordan

---

Ends With

WHERE FirstName LIKE '%son'

Matches:

• Johnson
• Anderson

---

Contains

WHERE FirstName LIKE '%oh%'

Matches:

• John
• Johnny

---

Single Character

WHERE Code LIKE 'A_1'

Matches:

• AA1
• AB1

But not:

• AAB1

because _ represents exactly one character.

---

Performance Matters

One of the biggest differences is how SQL Server can use indexes.

Using ‘=’

WHERE EmployeeId = 105

The database can efficiently perform an Index Seek, making this query very fast.

Using LIKE 'John%'

WHERE FirstName LIKE 'John%'

This can often still use an index because the search starts with a known prefix.

Using LIKE '%John%'

WHERE FirstName LIKE '%John%'

This is different.

Since the pattern begins with %, SQL Server usually cannot perform an Index Seek.

Instead, it scans many or all rows, which can significantly impact performance on large tables.

---

Common Mistake

Some developers use LIKE for exact matches:

WHERE FirstName LIKE 'John'

While it returns the same result as:

WHERE FirstName = 'John'

Using = is clearer, expresses your intent, and is generally the better choice for exact comparisons.

---

Best Practices

✔ Use = for exact matches.

✔ Use LIKE only when pattern matching is required.

✔ Avoid leading wildcards (%value) on large tables whenever possible.

✔ Ensure frequently searched columns are properly indexed.

Final Thoughts

The choice between = and LIKE isn’t just about syntax—it’s about selecting the right tool for the job.

If you’re searching for an exact value, = is the best choice.

If you need flexible text searching, LIKE is the right operator—but use it carefully, especially on large datasets.

Writing efficient SQL starts with understanding how the database executes your queries.