Lesson 14: Updating Tables in SQL

So far, you have looked at several different ways to retrieve and review your data. In this section, you will learn how to update your data. In the following two sections, you will learn about deleting and inserting rows. When you update, delete, and insert, you change the data -- you should perform these operations very cautiously. Before performing any of these operations on a production database, make sure your data is backed up and use the Start Transaction command. If you use the Start Transaction command, all of your changes are temporary until you commit your work and can be rolled back . If you have issued the Start Transaction command, you can undo your changes simply by typing "rollback work."


NOTE: The exercises that follow should not be performed on a production database. Use a test or trial database.

Syntax:

Start transaction;

Update tablename
set fieldname = value
where fieldname = value; 

Rollback work; 

Commit work;

Explanation:

• Issue a Start Transaction command before updating your table. This will allow you to roll back the changes, if necessary. If you do not issue a Start Transaction command, you will not be able the roll back your work.
• If you find that you have updated a row in error, execute the Rollback Work command.
• When you are satisfied with your changes, issue the Commit Work command.
• Use a Where clause to specify which rows will be updated. If you do not include a Where clause, all rows will be updated.
• Remember to end each command with a semicolon.

Example:Change the name of vendor "TV001" to Genie R. Corp., and then roll back the change. Then change the name of vendor "TV001" to Vanix and commit your work.

1. To start the transaction, type:

Start transaction;

2. Note the current vendor name, and type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

3. To update the vendor name, type:

Update TrnVendor
Set Name = 'Genie R Corp.'
Where VendId = 'TV001';

4. To check the vendor name to see that it has changed, type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

5. To roll back the change, type:

Rollback work;

6. To check the vendor name to see that it has reverted to the original, type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

7. To update the vendor name, type:

Update TrnVendor
Set Name = 'Vanix'
Where VendId = 'TV001';

8. To check the vendor name to see that it has changed, type:

Select VendId, Name
from TrnVendor 
where VendId = 'TV001';

9. To commit the change, type:

Commit work;

Note: You can run multiple SQL statements at the same time, as in the following example. To run all of the SQL statements in the previous exercise, type all of the commands in the SQL text box as shown here. Then click on First to execute the first statement and Next to execute each subsequent statement.

Start transaction; 
Select VendId, Name 
from TrnVendor 
where VendId = 'TV001'; 
Update TrnVendor 
Set Name = 'Genie R Corp.' 
Where VendId = 'TV001'; 
Select VendId, Name 
from TrnVendor 
where VendId = 'TV001'; 
Rollback work; 
Select VendId, Name 
from TrnVendor 
where VendId = 'TV001'; 
Update TrnVendor 
Set Name = 'Vanix Corp.' 
Where VendId = 'TV001'; 
Select VendId, Name 
from TrnVendor 
where VendId = 'TV001';