Talking to a SQL Server

Naturally, you can write more complicated UPDATE statements. To do so, you often use the same advanced search syntax that you use in your SELECT statements. Before running any blanket, potentially widespread UPDATE statements, why not try them out as a SELECT statement first? You have nothing to lose by doing so, and you can also spot errors before your data gets changed. Deleting Data Nothing lasts forever, including data. The time likely arises when you need to remove certain information from your SQL Server 2005 Express database. Several useful statements are at your disposal for removing this unneeded data. To begin, the DELETE statement offers an easy way to get rid of redundant rows in a particular table. In this case, suppose that you’re tired of seeing the lackluster sales for the Grape Gusher and Walnut Wipeout flavors. The time has come to remove them from your Flavors table: DELETE FROM Flavors Where FlavorName IN (‘Grape Geyser’,’Walnut Wipeout’) A DELETE statement without a WHERE clause obliterates all the rows in a table. Be very mindful when you invoke this statement. Because you have sales transactions in your Sales table, perhaps a better idea is to first remove the sales records, and then the flavor itself. Here’s how you would do that: DELETE FROM Sales WHERE FlavorID = (SELECT FlavorID FROM Flavors WHERE FlavorName = ‘Grape Geyser’) DELETE FROM Flavors WHERE FlavorName = ‘Grape Geyser’ If you have foreign key constraints in place, you must process DELETE statements in this order (that is, children first, parents last). A little earlier in this chapter, I placed a foreign key on the Sales table (refer to Table 9-2). The reason for doing this is to prevent erroneous deletion of parent (that is, Flavor) rows while child (that is, Sales) rows still exist. In fact, if you try