SfC Home > Web Design > SQL >
Explanation of Updating Data in SQL - Succeed in Using SQL. Also refer to Structured Query Language, MySQL, MS SQL Server, row, column, security, password, set, where, Ron Kurtus, School for Champions. Copyright © Restrictions
Updating Data in SQL
by Ron Kurtus (16 June 2007)
You can update 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. There is a mini-quiz near the end of the lesson.
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.
Always do your best
Resources
The following are resources on this subject.
Websites
Books
Mini-quiz to check your understanding
If you got all three correct, you are on your way to becoming a Champion in working with SQL. If you had problems, you had better look over the material again.
What do you think?
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. We will try to get back to you as soon as possible.
Share link
Feel free to establish a link from your website to pages in this site.
Or use our form to send this link to yourself or a friend.
Students and researchers:
The Web address of this page is
www.school-for-champions.com/sql/updating_data.htm.
Please include it as a reference in your report, document, or thesis.
Where can you go from here?
Updating Data in SQL