Updating Data

The UPDATE statement is used to update or change the existing data in the database. The syntax of UPDATE statement is given below.

UPDATE table_name
SET column_name1 = value1, column_name2 = value2,....
WHERE some_column = some_value;

The above syntax is just one way to represent this statement. The "where" clause can contain any combination of operators and as many conditions as needed. The omition of the "where" clause causes all the records in the table to be updated since it's the "where" clause which specifies which records have to be updated. So be careful !!

Consider the Emp_Stats table that we had discussed earlier. The table is again given below:

emp_id	salary	benefits	position
010	75000	15000	Manager
105	65000	15000	Manager
152	60000	15000	Manager
215	60000	12500	Manager
244	50000	12000	Staff
300	45000	10000	Staff
335	40000	10000	Staff
400	32000	7500	Entry-Level
441	28000	7500	Entry-Level

Have a look at the following query:

UPDATE Emp_Stats
SET salary = 80000
WHERE position = 'Manager' ;

This sets all the Manager's salary to 80000. The output of the above query is shown below:

emp_id	salary	benefits	position
010	80000	15000	Manager
105	80000	15000	Manager
152	80000	15000	Manager
215	80000	12500	Manager
244	50000	12000	Staff
300	45000	10000	Staff
335	40000	10000	Staff
400	32000	7500	Entry-Level
441	28000	7500	Entry-Level

A word of caution: In case you forget to put the "where" clause, the value of "salary" in all the records would be set to "80000" and the original value will be lost. There is no way to restore the changes made. So be careful while using this command..!!