Programmer need to modify the data in the database when the specifications of a customer, a client, a transaction, or any other data maintained by the organization undergo a change.
For example, if a client changes his address or if the quantity of a product ordered is changed, the required changes need to be made to the respective rows in the tables. You can use the UPDATE statement to make the changes. Updating ensures that the latest and correct information is available at any point of time. One column of a row is the smallest unit of an update.
You can update data in a table by using the UPDATE DML statement. The syntax of the UPDATE statement is:
UPDATE table_name SET column_name = value [, column_name = value]
[FROM table_name]
[WHERE condition]
Where,
The following statement updates the AddressLine2 attribute of AddressID 104
UPDATE Address
SET AddressLine2 = ‘Plaza Palace’
WHERE AddressID = 104
Consider another example where you need to update the Title of an employee, Lynn Tsoflias to ‘Sales Executive’, in the Employee table. To perform this task, you need to refer to the contact table to obtain the Contact ID. You can update the details by using the following statement:
UPDATE HumanResources.Employee SET Title = ‘Sales Executive’
FROM HumanResources.Employee e, Person.Contact c
WHERE e.contactID = c.ContactID
AND c.FirstName = ‘Lynn’ and c.LastName = ‘Tsoflias’
When the preceding command is executed, the Title will be changed to ‘Sales Executive’.
Delete data from database and related table.
For example, if a client changes his address or if the quantity of a product ordered is changed, the required changes need to be made to the respective rows in the tables. You can use the UPDATE statement to make the changes. Updating ensures that the latest and correct information is available at any point of time. One column of a row is the smallest unit of an update.
You can update data in a table by using the UPDATE DML statement. The syntax of the UPDATE statement is:
UPDATE table_name SET column_name = value [, column_name = value]
[FROM table_name]
[WHERE condition]
Where,
- Table_name specifies the name of the table you have to modify.
- Column_name specifies the columns you have to modify in the specified table.
- Value specifies the value(s) with which you have to update the column(s) of the table. Some valid values include an expression, a column name, and a variable name. The DEFAULT and NULL keywords can also be supplied.
- FROM table_name specifies the table(s) that is used in the UPDATE statement.
- Condition specifies the rows that you have to update.
Guidelines for Updating Data
You need to consider the following guidelines while updating data:- An update can be done on only one table at a time.
- If an update violates integrity constraints, then the entire update is rolled back.
The following statement updates the AddressLine2 attribute of AddressID 104
UPDATE Address
SET AddressLine2 = ‘Plaza Palace’
WHERE AddressID = 104
Consider another example where you need to update the Title of an employee, Lynn Tsoflias to ‘Sales Executive’, in the Employee table. To perform this task, you need to refer to the contact table to obtain the Contact ID. You can update the details by using the following statement:
UPDATE HumanResources.Employee SET Title = ‘Sales Executive’
FROM HumanResources.Employee e, Person.Contact c
WHERE e.contactID = c.ContactID
AND c.FirstName = ‘Lynn’ and c.LastName = ‘Tsoflias’
When the preceding command is executed, the Title will be changed to ‘Sales Executive’.
Delete data from database and related table.