UPDATE COMMAND IN SQL
The UPDATE command in SQL updates rows in a table. Only the table owner, a DBA, a SYSADM, or a user with the UPDATE privilege for the entire table or for the specific column may execute the command.
When updating a column the new column values must satisfy the column constraints and referential integrity. Use the DEFAULT keyword to set the value of the column to the default.
The basic syntax of the UPDATE command (SQL) is as follows:
UPDATE table_name SET column_name = value WHERE condition
Typically, the values of data items stored in a database are modified when corresponding changes occur in the outside world. For example, in the sample database:
• When a customer calls to change the quantity on an order, the QTY column in the appropriate row of the ORDERS table must be modified.
• When a manager moves from one office to another, the MGR column in the OFFICES table and the REP_OFFICE column in the SALESREPS table must be changed to reflect the new assignment.
• When sales quotas are raised by 5 percent in the New York sales office, the QUOTA column of the appropriate rows in the SALESREPS table must be modified.
Simple Updates:
Example 1
The following shows how to update the Employees table and change the salary of all employees named “Chris”.
UPDATE Employees SET Salary = 5000 WHERE Name = ‘Chris’;
Example 2
The following shows how to give a salary raise of 10% to all employees named “Chris”.
UPDATE Employees SET Salary = Salary*1.10 WHERE Name = ‘Chris’;
New Values from a Subquery:
You can also generate new values from a sub-query. One way to do this is to write separate sub-queries for each column that you are updating:
UPDATE table SET column = (subquery), column = (subquery);
Example 1
The following example shows how to update owner_id for table upfall based on id column from owner table:
UPDATE upfall u SET owner_id = (SELECT o.id FROM owner o WHERE o.name = 'Pictured Rocks') WHERE u.name = 'Miners Falls';
Such subqueries must always return zero or one row and one column. If zero rows are returned, then the value is set to null.
In DB2 and Oracle, you can also write a subquery that returns more than one column value, in which case the number of values returned must correspond to the columns you are updating:
UPDATE table SET (column, column, ...) = (subquery)
Example 2
This example update names and descriptions with any new information in the new_falls table:
UPDATE upfall u SET (u.name, u.description) = (SELECT nf.name, nf.description
FROM new_falls nf WHERE u.id = nf.id) WHERE u.id IN (SELECT nf2.id
FROM new_falls nf2);
Be careful with this kind of update. If you omit the WHERE clause in this query, all rows in upfall will be updated, regardless of whether corresponding rows exist in new_falls. Worse, upfall’s name and description columns will be set to null in cases where no corresponding new_falls rows exist.
Updating Views and Subqueries:
All platforms allow UPDATEs to run against views. DB2 and Oracle also allow updates to run against subqueries (i.e., inline views):
UPDATE (SELECT * FROM upfall WHERE owner_id IS NULL) SET open_to_public = 'n';
Database systems place various restrictions on the updating of views, but in general, you must be able to access unambiguously a single table row from a given view row in order to issue an update against that view (or subquery).