Previous | Table of Contents | Next |
Once you update a column, the change is permanent. There is noundo feature.
You may update only one table at a time. If you are joiningtables in your update, you must be sure to update columns from onlya single table. Likewise, if you are updating a view, and the viewreferences multiple tables, you must perform the UPDATEin two steps.
The titleview view in the pubs database joins titles, authors,and titleauthor together to list several columns from each table.Here is a sample of the output from the titleview view:
title au_ord au_lname price ytd_sales pub_id ---------------------------------- ------ -------- ----- --------- ----- The Busy Executives Database Guide 2 Green 19.99 4095 1389 The Busy Executives Database Guide 1 Bennet 19.99 4095 1389
If the Busy Executives book was sold to a different publisher (publisher ID 0736), and the publisher wanted to reversethe order of the authors names, here is an UPDATEstatement to change this information:
/* This update fails! */ update titleview set pub_id = 0736, au_ord = 1 where title = The Busy Executives Database Guide and au_lname = Green /* This update fails! */ update titleview set pub_id = 0736, au_ord = 2 where title = The Busy Executives Database Guide and au_lname = Bennet
Msg 4405, Level 16, State 2 View titleview is not updatable because the FROM clause names multiple tables.
I tried to update au_ord, which lives in titleauthor, andpub_id, which lives in titles, in the same UPDATEstatement. The error message suggests that the view isntupdatable in any case, but that isnt true. Heres howto actually perform these updates:
/* First, update the pub_id. */ update titleview set pub_id = 0736 where title = The Busy Executives Database Guide /* Now, change the au_ord for Green */ update titleview set au_ord = 1 where title = The Busy Executives Database Guide and au_lname = Green /* Last, change the au_ord for Bennet */ update titleview set au_ord = 2 where title = The Busy Executives Database Guide and au_lname = Bennet
(1 row(s) affected) (1 row(s) affected) (1 row(s) affected)
Using the titleview view, I updated information in thetitles table with the first UPDATE statement. Althoughtitleview displays two rows for this title, there is really onlyone row in titles for this book. This is why I got a 1 row(s)affected message instead of two rows.
The author order is adjusted in a separate step. After all is said and done, the titles table has a book with a new publisher,and Green and Bennet have had their author orders reversed.
TIP: Performance tip
When working with large tables, try to perform as many columnchanges as possible in a single UPDATE statement. If youchange two columns in one update, the row needs to be read andwritten only once. If you change one column in two UPDATEstatements, each row needs to be read twice and writtentwice.
Starting with MS SQL Server v6.5 you can update a row in a tableand set the column back to its default value. The inventory table,created earlier today, has a default assigned to the qty column.Suppose these values exist in the inventory table:
inv_id item_id qty ----------- ----------- -- 1 45 12 2 46 13
To update the last row and set the quantity back to its defaultvalue of 1, use this query:
update inventory set qty = DEFAULT where inv_id = 2
inv_id item_id qty ------- -------- --- 1 45 12 2 46 1
Use the DEFAULT keyword to set a column to thedefault value. This is only usable when a default has been definedon the column.
TIP:
If you want to set all rows to the default columnvalue, issue the UPDATE without restricting the rows onwhich it will operate.
Here is the syntax for the UPDATE statement:
update <tablename> | <viewname> | <alias> set <column> = <New value>[, <column> = <New value>...] [from <table list>] [where <boolean constraint>]
To remove rows from a table, use the DELETE statement.The DELETE removes a row from my inventory table:
delete inventory where inv_id = 1
(1 row(s) affected)
Just as with the UPDATE statement, be verycareful about qualifying the rows you want to affect with aWHERE clause. A DELETE without a WHEREclause deletes all the rows in a table!
Watch the row counts as they come back from a DELETEoperation. Sometimes, you may delete no rows at all. If I tried todelete from the inventory table and specified an inv_id thatdidnt exist, here is what Id get back:
delete inventory where inv_id = 100
(0 row(s) affected)
Issuing a DELETE that affects no rows does not cause an error. This is an especially important point when developing application programs. If you intend to delete a row, issue a DELETE, and find that you have deleted nothing, there is a very good chance there is a problem. Different programming libraries deal with this problem in different ways.
The same rules that apply to table joins and updates apply to table joins and deletes. You may delete from only one table at a time, but you may join to other tables for information on which rows to delete. You must use non-ANSI join syntax, or a subquery, to perform a table join inside a DELETE.
Previous | Table of Contents | Next |