In addition to querying and inserting data, we will need to change some data from time to time. The most important component of any data change is the WHERE clause. Why is this so important? If you fail to include a WHERE clause the change will affect EVERY row in the table!! Many a horror story can be told about failing to put a WHERE clause before a change and then having to recover data as a result.
**Warning** We will get into the syntax, but remember the WHERE clause for EVERY change you make—you have been warned. 🙂 If you don’t have a backup–now might be a good time to take one.
As the name implies, when we want to make a change to the data, we will use the keyword UPDATE. We then indicate what table we are going to update. The keyword SET indicates which columns we want to change. The most important component is our WHERE clause which puts limits on the rows that will be updated.
UPDATE Cat SET NameOfCat = 'Mr. Whiskers' WHERE NameOfCat = 'Whiskers';
(4 row(s) affected)
This changes the NameOfCat of ‘Whiskers’ to ‘Mr. Whiskers’. Don’t forget to put single quotes around all the text options.
We can continue to build on what we have learned and do more complex statements. Here we are going to update the MonthsWithOwner column of the cat table because we found an error with owners from Russia. We only want to update those records, so we can use the INNER JOIN syntax and put a WHERE clause to update only the records we want.
UPDATE Cat SET MonthsWithOwner = MonthsWithOwner + 2 FROM Cat INNER JOIN PetOwner ON PetOwner.OwnerIdentifier = Cat.OwnerIdentifier WHERE PetOwner.CountryOfResidence = 'Russia';
(2 row(s) affected)
We will want to remove data—occasionally—maybe. Our databases are getting bigger and bigger because we are keeping more data. In fact, many times I normally move old data to another table instead of deleting it; however, we do run across data we do want to delete. We use the keyword DELETE followed by the table we are deleting from. We will generally (99% of the time) include a WHERE clause, unless you want to remove ALL the data from the table. You have been warned—don’t forget the WHERE clause. In this example, 2 test records have been created we can delete.
DELETE FROM Dog WHERE Dog.NameOfDog = 'Test';
(2 row(s) affected)
**Advanced point** Many applications use a flag to indicate if a record has been deleted. There may be a column called IsDeleted where 0 means the record is usable and 1 means the record should not be used by the application. In virtually all of the SELECT statements, we would need to add a WHERE IsDeleted = 0 to get the rows we care about. Another option to remove data is to archive the data to another table. We did mention how to move data from one table to another; however, the process of archiving data is beyond the scope of this book.