Update (SQL)

From wiki.gis.com
Jump to: navigation, search

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.

Patients
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'
Patients
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

See Also