Modifying Data

It is also easy to modify an existing row using SQL. To do so, you will use the UPDATE operator which follows the basic format of:

    UPDATE table_name
    SET column_name = some_value
    WHERE where_clause;

For example, if we wanted to change the EMPLOYEES table such that Lim Sing Yuen received a $5,000 raise we would say:

    UPDATE EMPLOYEES
    SET  EMP_SALARY = (EMP_SALARY + 5000)
    WHERE EMP_NAME = 'Lim Sing Yuen';

or more directly:

    UPDATE EMPLOYEES
    SET  EMP_SALARY = 45000
    WHERE EMP_NAME = 'Lim Sing Yuen';

Note also that you can easily modify more than one column at once in the SET clause. For example, you might say:

    UPDATE EMPLOYEES
    SET  EMP_SALARY = 50000,
	 EMP_COMMISSION = '15%'
    WHERE EMP_NAME = 'Lim Sing Yuen';

You should also be aware, that if you did not specify a WHERE clause, then the operation affects all rows in the table. Thus the following code would give every employee a $5000 raise:

    UPDATE EMPLOYEES
    SET  EMP_SALARY = (EMP_SALARY + 5000);

Finally, note that the WHERE clause can be as complex as needed, including subqueries if desired. Consider this example in which we give raises to only those employees who make less than the average salary:

    UPDATE EMPLOYEES
    SET  EMP_SALARY = (EMP_SALARY + 1000);
    WHERE EMP_SALARY <
	(SELECT AVG(EMP_SALARY)
	 FROM EMPLOYEES);

Adding Data
Introduction to Databases for the Web | Table of Contents
Delete