Lesson 3: Modifying Data | Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit

You can modify data with subqueries by using the INSERT SELECT statement and delete or update data in a table based on data from another table by using the DELETE or UPDATE statements.

After this lesson, you will be able to:

Insert data into an existing table from another table using the INSERT SELECT statement

Use subqueries and the FROM clause to use other tables when specifying criteria for the DELETE and UPDATE statements

Estimated lesson time: 60 minutes

Using the INSERT SELECT Statement

In Appendix D, "Querying Multiple Tables," you learned about the SELECT INTO statement, which allowed you to create a new table from the result set of a SELECT statement. The INSERT SELECT statement adds rows to an existing table by inserting the result set of a SELECT statement into the table.

Using the INSERT SELECT statement is more efficient than writing multiple, single-row INSERT statements. When you use the INSERT SELECT statement, consider the following facts and guidelines:

All rows that satisfy the SELECT statement are inserted into the outermost table of the query.

The table that receives the new rows must already exist in the database.

The columns of the table that receives the new rows must have data types that are compatible with the columns of the table source.

Determine whether a default value exists or whether a null value is allowed for any columns that are omitted from the SELECT statement. If there is no default and null values are not allowed, you must provide values for these columns.

Syntax

INSERT [INTO] table_name
  SELECT select_list
  FROM table_list
  WHERE search_conditions

Example

This example adds new customers to the Customers table. Employees of Northwind Traders are eligible to buy company products. This query contains an INSERT statement with a SELECT clause that adds employee information to the Customers table.

The value for the CustomerID column will be generated from the first three letters of the employee s first name, concatenated with the first two letters of the last name. The employee s last name is used in the CompanyName column, and the first name is used in the ContactName column.

 USE Northwind  INSERT Customers    SELECT UPPER(SUBSTRING(FirstName, 1, 3) + SUBSTRING(LastName, 1, 2)),      LastName, FirstName, Title, Address, City,      Region, PostalCode, Country, HomePhone, NULL     FROM Employees

Exercise 2: Inserting Values from a Query into an Existing Table

In this exercise, you will insert rows into the adult table of the library database for all juvenile members over age 18 by using the SELECT clause of the INSERT statement. You will delete the rows from the juvenile table in another exercise. The C:\SQLDW\Exercise\AppE\juv2adlt.SQL script file contains a completed script for this exercise.

To write an INSERT statement that inserts data from a query into an existing table

Write a SELECT statement that returns the member_no column from the juvenile table and the street, city, state, zip, and phone_no columns from the adult table. Also include in the select list today s date plus one year by using the following expression:

 (DATEADD(YY, 1, GETDATE())

This last column will be used later, to provide a value for the adult.expr_date column. This SELECT statement joins the juvenile table with the adult table; the join condition is juvenile.adult_member_no = adult.member_no.

Write an INSERT statement that incorporates the SELECT statement that you created in step 1 in order to add rows to the adult table. Include a WHERE clause to limit the rows that are added to those members in the juvenile table who are over age 18 by using the following expression:

 (DATEADD(YY, 18, ju.birth_date) < GETDATE()) USE library INSERT adult  SELECT      ju.member_no,      ad.street,      ad.city,      ad.state,      ad.zip,      ad.phone_no,      DATEADD(YY, 1, GETDATE())  FROM juvenile ju JOIN adult ad  ON ju.adult_member_no = ad.member_no  WHERE (DATEADD(YY, 18, ju.birth_date) < GETDATE())

Execute the query.

Write a query to verify that juvenile members were added to the adult table. The C:\SQLDW\Exercise\AppE\juv_test.SQL script file is an example of this query.

Deleting Rows Based on Other Tables

Use the DELETE statement with joins or subqueries to remove rows from a table based on data stored in other tables. This is more efficient than writing multiple, single-row DELETE statements.

Using an Additional FROM Clause

In a DELETE statement, the WHERE clause references values in the table itself and is used to decide which rows to delete. If you use a FROM clause, you can reference other tables to make this decision. When you use the DELETE statement with a FROM clause, consider the following facts:

You can use the optional FROM keyword before the table name from which the rows are deleted. Do not confuse this keyword with the FROM clause.

The FROM clause may introduce a join and acts as the restricting criteria for the DELETE statement.

Partial Syntax

DELETE [FROM] {table_name | view_name}
[FROM {table_or_view | joined_table}[, n]]
[WHERE {search_condition | CURRENT OF cursor_name}]

Example 1

This example uses a join operation with the DELETE statement to remove customers who reside in the United States (USA) from the orders table of the Northwind database. (You cannot actually execute this example, because it will violate referential integrity constraints.)

 USE Northwind  DELETE FROM Orders  FROM Customers c INNER JOIN Orders o  ON c.CustomerID = o.CustomerID  WHERE Country = 'USA'

Specifying Conditions in the WHERE Clause

You also can use subqueries to determine which rows to delete from a table based on rows of another table. You can specify the conditions in the WHERE clause rather than using an additional FROM clause. Use a nested or correlated subquery in the WHERE clause to determine which rows to delete.

Example 2

This example removes the same customers as Example 1 and shows that you can convert a join operation to a nested subquery. (You cannot actually execute this example, because it will violate referential integrity constraints.)

 USE Northwind  DELETE FROM Orders  WHERE CustomerID IN     (SELECT CustomerID     FROM Customers     WHERE Country = 'USA')

Exercise 3: Deleting Rows from a Table Based on Data in Other Tables

In this exercise, you will create a query that deletes rows from the juvenile table that have matching rows in the adult table of the library database. After juvenile members are converted to adult members, those members must be deleted from the juvenile table. The C:\SQLDW\Exercise\AppE\del_juv1.SQL script file contains a completed script for this exercise.

To write a DELETE statement that uses data in other tables for the deletion criteria

Write a SELECT statement that joins the adult and juvenile tables so that juvenile.member_no = adult.member_no.

Write a DELETE statement that uses the SELECT statement that you created in step 1 with an additional FROM clause to delete these rows from the juvenile table.

 USE library  DELETE FROM juvenile  FROM juvenile INNER JOIN adult  ON juvenile.member_no = adult.member_no

Execute the query.

Write a SELECT statement to verify that the juveniles that were added to the adult table have been removed from the juvenile table. The C:\SQLDW\ Exercise\AppE\del_test.SQL script file is an example of this query.

Updating Rows Based on Other Tables

Use the UPDATE statement with a FROM clause or a subquery to modify a table based on values from other tables.

Using the UPDATE Statement

When you use joins and subqueries with the UPDATE statement, consider the following facts and guidelines:

SQL Server never updates the same row twice in a single UPDATE statement. This built-in restriction minimizes the amount of logging that occurs during updates.

Use the SET keyword to introduce the list of columns or variable names to be updated. Any column prefix that is specified in the SET clause must match the table or view name that is specified after the UPDATE keyword.

Tables introduced by subqueries or the FROM clause cannot be updated in the UPDATE statement.

Partial Syntax

UPDATE {table_name | view_name}
SET column_name={expression | DEFAULT | NULL} [, n]
{[FROM {table_or_view | joined_table}[, n]]
[WHERE search_conditions ]}
| WHERE CURRENT OF cursor_name

Using Joins

Use the FROM clause to specify tables and joins that are used to provide the criteria for the UPDATE statement.

Example 1

This example uses a join to update the number of sales to date of each product in the Products table. Assume that a ToDateSales column has been added to the Products table. This query does not generate the expected results. Each row in the Products table is updated only once, so only the first order of each product in the order details table is added to the ToDateSales column.

 USE Northwind  UPDATE Products  SET ToDateSales = ToDateSales + od.Quantity  FROM Products INNER JOIN [Order Details] od  ON Products.ProductID = od.ProductID

Using Subqueries

When you use subqueries to update rows, consider the following facts and guidelines:

SQL Server executes the subquery once for each row in the table to be updated. Therefore, the subquery must return a single value for each row.

If the subquery does not return a single value, the results are unspecified. Possible results are NULL values, errors or expected results.

Consider using aggregate functions with subqueries because SQL Server never updates the same row twice in a single UPDATE statement.

Example 2

This example solves the problem of Example 1, where only the first order of each product in the Products table was updated. Many orders for each product may exist. Because SQL Server never updates the same row twice, you can use an aggregate function with a correlated subquery to update the total number of sales to date of each product.

 USE Northwind  UPDATE Products  SET ToDateSales = (SELECT SUM(Quantity)                                         FROM [Order Details] od                                         WHERE Products.ProductID = od.ProductID)

Exercise 4: Updating Rows in a Table Based on Data in Other Tables

In this exercise, you will create a query that updates rows in the copy table to set the on_loan column to Y for those books that are represented by a copy_no/isbn that are on loan. Because the loan table contains a list of all copy_no/isbn pairs that are on loan, it is referenced to update the copy table.

To write an UPDATE statement that uses data in other tables for the update criteria

Review and execute the C:\SQLDW\Exercise\AppE\updsetup.SQL script, which adds a new title and copy of a book to the library database. At this point, the copy is not on loan.

Review and execute the C:\SQLDW\Exercise\AppE\loan_out.SQL script, which places this copy on loan. Notice that this script updates the loan table but does not update the copy table.

Write and execute a SELECT statement that joins the copy and loan tables and returns all matching rows. Use a WHERE clause to limit the rows to those for which the copy.on_loan is not Y.

Be aware that both tables have a compound primary key, so you must reference both columns in the JOIN clause. The C:\SQLDW\Exercise\ AppE\upd_cpy1.SQL script file contains a completed script for this step.

 USE library  SELECT *   FROM copy JOIN loan  ON copy.isbn = loan.isbn AND copy.copy_no = loan.copy_no   WHERE copy.on_loan <> 'Y'

Write and execute an UPDATE statement that uses the FROM and JOIN clauses of the SELECT statement that you created in step 3 to update the copy.on_loan column to Y for all copies that are on loan but do not have a copy.on_loan value of Y.

The C:\SQLDW\Exercise\AppE\upd_cpy2.SQL script file contains a completed script for this step.

 USE library  UPDATE copy  SET on_loan = 'Y'  FROM copy JOIN loan  ON copy.isbn = loan.isbn AND copy.copy_no = loan.copy_no   WHERE copy.on_loan <> 'Y'

Execute the query.

Verify that the copy.on_loan column was changed from N to Y for copy_no = 1 and isbn = 10005. The C:\SQLDW\Exercise\AppE\upd_test.SQL script file is an example of this query.

Lesson Summary

The INSERT, UPDATE, and DELETE statements all support advanced options for modifying data in a table based on the data from other tables.

The INSERT SELECT statement allows you to insert data into an existing table from tables in the same or other databases.

Subqueries can be used in the SET clause of the UPDATE statement to calculate column values. Subqueries can also be used in the WHERE clause of the UPDATE and DELETE statements to specify the criteria for which rows should be updated or deleted.

The FROM clause of the UPDATE and DELETE statements is used to join other tables to the table being modified. The joined table(s) are then available for specifying the criteria for which rows should be updated or deleted.