SQL Update statements allow you to modify your existing data without having to add a new row or delete and replace data with the ‘corrected’ version.  This process can be very useful in correcting data errors, and ensuring your data is current.

In our example table tblChair we are going to assume that all of our chairs are suppose to have 4 legs.  We also know, by looking at our data, chair ID 1 and chair ID 3 do not have 4 legs.

In order to correct this data error, we will utilize an SQL UPDATE statement.  There are a couple of things we will want to remember to do in order to ensure our changes are recorded and that we have made the changes to the correct data.

1. Use a WHERE clause in your update statement.
2. Use a COMMIT upon completion of the UPDATE statement.

We have seen the WHERE clause from the SELECT statement, so if you need a review, head back to the Selects page.  COMMIT is something new.  What the commit does is essentially tell the database engine to write your changes to the database basically ‘save’ your requested modifications.

UPDATE <Table Name>

SET  <Column Name> [= < > != BETWEEN NOT]  [New Value]

WHERE <Column Name> [= < > != BETWEEN NOT] <Column Name or Value> [AND/OR] [<Column Name> [= < > != BETWEEN NOT] etc.];

COMMIT;

Note:  You are really executing 2 statements here, 1 to make your changes, the other to ‘save’ them.  Can use multiple UPDATE, INSERT and DELETE statements when working with your data, and COMMIT them all in one command.  Use the COMMIT statement sparingly in your code, as overuse can drive up your processing overhead.  When you are sure you are done, commit away.

Back to our example, the statement you can use to update our offending records is shown below:

UPDATE tblChair

SET  nLegs = 4

WHERE nLegs < 4;

COMMIT;

nID

nLegs

strColor

strSize

strType

nWeight