UPDATE and DELETE in SQL Server

This article is meant for people who are just learning SQL.

DML commands typically touch the database through apps; users don’t sit down and manually update or delete records. This is a common instance. However, the developers prepare a script with delete/update statements in the event that data fix is necessary to resolve a particular scenario. DML statements will therefore be encountered by both applications and developers.

When issuing a DML statement against a database, one must be careful.

Let’s see how to use UPDATE and DELETE statements with different options:

UPDATE tbEmployee SET Sal = Sal + 1000;
Due to the absence of a “WHERE” clause, this statement will update ALL the records in the table.

UPDATE tbEmployee SET Sal = Sal + 1000 WHERE EmpNo = 1089;
This statement will modify a particular table record. When the search conditions (the WHERE clause) match, the record will be updated.

UPDATE tbEmployee SET Sal = Sal + 1000 FROM tbEmployee e
JOIN tbDept d ON e.deptno = d.deptno
WHERE DName = ‘Accounting’;
This statement will only change the table records that match the join and search conditions.

DELETE FROM tbEmployee;
Due to the absence of a “WHERE” clause, this statement will delete ALL the records in the table.

DELETE FROM tbEmployee WHERE EmpNo = 1089;
A specific table record will be deleted by this statement. The record will be updated if the search criteria (the WHERE clause) match.

DELETE FROM tbEmployee e
JOIN tbDept d ON e.deptno = d.deptno
WHERE DName = ‘Accounting’;
This statement will eliminate the matched records when the join condition and the search condition coincide.