SfC Home > Web Design > SQL >
Updating Data with SQL
by Ron Kurtus (revised 18 September 2012)
You can update or modify the data of a specific item in your database table. You can also update several items in a row. For security purposes, you may need database administrative privileges to update the data.
Also, care must be taken not to accidentally update every row in the database.
Questions you may have include:
- How do you update a specific item in the table?
- How do you update several items in a row?
- What precautions should be made?
This lesson will answer those questions.
Update specific item
Consider the simple database table, entitled animal_colors:
animal_id |
animal |
color |
| 3 | Cat | White |
| 4 | Dog | Black |
| 5 | Skunk | Black and White |
| 7 | Dog | White |
If you wanted to update the color of a specific dog, your SQL code would be:
UPDATE animal_colors
SET color = 'Yellow'
WHERE animal_id = '4';
You designate the table to update, state what you want to set, and state which row is to be changed. Your SQL statement ends with a semi-colon (;).
Several items
You can also update several items at a time.
Similar items
If you wanted to update the color of all dogs in the table:
UPDATE animal_colors
SET color = 'Yellow'
WHERE animal = 'Dog';
In this case, all rows that have the animal as a Dog are updated.
Several in one row
If you wanted to update several items in a single row, your SQL would be:
UPDATE animal_colors
SET animal = 'Hamster', color = 'Orange'
WHERE animal_id = '7';
You list the items to be updated, separated by a comma (,). No comma is used at the end of the SET statement.
Precautions
Since an unauthorized person updating data in the database could compromise the integrity of the data, often a password is required to update the data.
Also, it is very important to make sure you include the WHERE statement. If that designation is missing, you could accidentally update every row in your database table.
Summary
You can update the data of a specific item, in your database table, as well as several items in a row. Precautions include requiring a password to update the data. Also, care must be taken not to accidentally update every row in the database.
Share your knowledge
Resources and references
Websites
Books
(Notice: The School for Champions may earn commissions from book purchases)
Students and researchers
The Web address of this page is:
www.school-for-champions.com/sql/
updating_data.htm
Please include it as a link on your website or as a reference in your report, document, or thesis.
Where are you now?
Updating Data with SQL