To update one or more rows, use the UPDATE statement. The new data can be a constant or an expression that you specify, or data pulled from other tables.
As in all data modification statements, you can change the data in only one table or view at a time.
If an UPDATE statement violates an integrity constraint, the update does not take place and an error message appears. For example, if one of the values being added is the wrong data type, or if it violates a constraint defined for one of the columns or data types involved, the update does not take place.
A simplified version of the syntax is:
UPDATE table-name SET column_name = expression WHERE search-condition
If the company Newton Ent. (in the Customers table of the demo database) is taken over by Einstein, Inc., you can update the name of the company using a statement such as:
UPDATE Customers SET company_name = 'Einstein, Inc.' WHERE company_name = 'Newton Ent.'
You can use any condition in the WHERE clause. If you are unsure how the company name was entered, try updating any company called Newton, with a statement such as the following:
UPDATE Customers SET company_name = 'Einstein, Inc.' WHERE company_name LIKE 'Newton%'
The search condition need not refer to the column being updated. The company ID for Newton Entertainments is 109. As the ID value is the primary key for the table, you could be sure of updating the correct row using:
UPDATE Customers SET company_name = 'Einstein, Inc.' WHERE id = 109
The SET clause specifies the columns to be updated, and their new values. The WHERE clause determines the rows to be updated. If you do not use a WHERE clause, the specified columns of all rows are updated with the values in the SET clause.
You can provide any expression of the correct data type in the SET clause.