Update (SQL)
The SQL UPDATE
statement changes single or multiple records in a database table. All records may be changed unless a conditional WHERE
clause is used. The UPDATE
statement has the following general form:
UPDATE TABLE_NAME SET Column_name = VALUE [, COLUMN name = VALUE...] [WHERE condition]
Consider a database table named "Providers" that contains information about various companies that provide goods or services to a business. The Providers table may have columns for item names, SKU numbers, item descriptions, prices, etc. Suppose a new supplier is found for printers, and you want to update the company name in the Supplier column from "ABC" to "ACME."
The following unconditional statement will change the values in the Supplier column to ACME for every record.
UPDATE Providers SET Supplier = 'ACME'
However, if there are other items listed in the table from various suppliers and the ABC company only provides printers, the following statement may be used to update all records in the table only where the supplier is ABC:
UPDATE Providers SET Supplier = 'ACME' WHERE Supplier = 'ABC'
Suppose that ABC company also provides other items; the following statement only updates table rows where the Item type is "printer."
UPDATE Providers SET Supplier = 'ACME' WHERE Item = 'printer'
Let us further suppose that the new supplier is only providing a single model of printer, Model A5560. Use the following statement to update the table where the item is "printer," and the model is "A5560."
UPDATE Providers SET Supplier = 'ACME' WHERE Item = 'printer' AND Model = 'A5560'
Consider the following "Patients" table for a medical office. The patients are listed alphabetically by name, and three members of the Anderson family are listed: Lee, Moira and William. Thomas Anderson is an unrelated patient. The Anderson family has moved to a new address, 5557 Louis Road, and the table needs to be updated to reflect that change. However, another patient, Mary Artest, lives at the same address who is not moving, so we do not want to change the address for either Thomas Anderson or Mary Artest.
Last_name | First_name | Street_address |
---|---|---|
Allen | James | 19951 O'Farrell Street |
Anderson | Lee | 2220 Monroe Avenue |
Anderson | Moira | 2220 Monroe Avenue |
Anderson | Thomas | 35775 Redmond Road |
Anderson | William | 2220 Monroe Avenue |
Andrews | Jeff | 2346 Geary Blvd. |
Artest | Mary | 2220 Monroe Avenue |
Use the following UPDATE
statement to change the address for the Anderson family.
UPDATE Patients SET Street_address = '5557 Louis Road' WHERE Last_name = 'Anderson' AND Street_address = '2220 Monroe Avenue'
Last_name | First_name | Street_address |
---|---|---|
Allen | James | 19951 O'Farrell Street |
Anderson | Lee | 5557 Louis Road |
Anderson | Moira | 5557 Louis Road |
Anderson | Thomas | 35775 Redmond Road |
Anderson | William | 5557 Louis Road |
Andrews | Jeff | 2346 Geary Blvd. |
Artest | Mary | 2220 Monroe Avenue |
References
- SQL: UPDATE Statement. TECH on the Net website, Accessed 8 June 2010.