Manipulating Data — UPDATE and DELETE

Two DML commands change data already stored in a table: UPDATE modifies the values in existing rows, and DELETE removes whole rows. Both almost always use a WHERE clause to say which rows they act on; leaving the WHERE clause out makes them affect every row in the table, which is a common and serious mistake.

UPDATE — changing values. The form is UPDATE table SET column = value [, column = value ...] WHERE condition;. A single UPDATE can change several columns at once, and the new value may be a calculation based on the existing value. For example, to raise the salary of employee 103 to 30000:

UPDATE Employee
SET Salary = 30000
WHERE EmpNo = 103;

To give every IT-department employee a 10% raise, using the current salary in the calculation:

UPDATE Employee
SET Salary = Salary * 1.10
WHERE Dept = 'IT';

To change two columns in one statement — move employee 102 to the Accounts department and set a new salary:

UPDATE Employee
SET Dept = 'Accounts', Salary = 31000
WHERE EmpNo = 102;

DELETE — removing rows. The form is DELETE FROM table WHERE condition;. For example, to remove the record of employee 106:

DELETE FROM Employee
WHERE EmpNo = 106;

To remove all employees of the Sales department:

DELETE FROM Employee
WHERE Dept = 'Sales';
``` …