Oracle:UPDATE

Main Page >> Oracle and SQL >> Workbook >> DML >> Updating records

Modifying Rows

Existing data can be modified using the UPDATE command. The format of this command is:

UPDATE TableName1
SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2 ...]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]


Where:

• UPDATE TableName1 Specifies the table in which records are updated with new values.
• SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2 Specifies the columns that are updated and their new values.

If you omit the WHERE clause, every row in the column is updated with the same value.

• WHERE FilterCondition1 [AND | OR FilterCondition2 ...]] Specifies the records that are updated with new values. FilterCondition specifies the criteria that records must meet to be updated with new values.

You can include as many filter conditions as you like, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or use IS NULL to check for an empty field.

Note: SQL can only update one table at a time.

To update CLARK's manager and department number, type:

UPDATE EMP 
   SET MGR = 7566, DEPTNO = 20
   WHERE ENAME = 'CLARK';
COMMIT;

Exercise 3.5

Why could this last command produce unexpected results?

Next Step

This completes the section on Data Manipulation Commands. The next step is the Data Definition Language Commands, or return to the Workbook.