SfC Home > SQL >

Explanation of Updating Data in SQL - Succeed with SQL. Also refer to 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:

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.

Answers to Readers' Questions

Resources

The following are resources on this subject.

Websites

SQL Resources

Books

Top-rated books on SQL

Miscellaneous

Mini-quiz to check your understanding

1. Why would the ID be usually used in the WHERE command for updating a specific item?

2. How do you separate items in the SET command?

3. When should you omit using the WHERE command?

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?

School for Champions

SQL topics

Updating Data in SQL