SELECT , INSERT , UPDATE , and DELETE
SELECT , INSERT , UPDATE , and DELETEFour basic SQL statements allow you to retrieve and modify data in tables. SELECT retrieves data from one or more tables, INSERT inserts rows into one table, UPDATE modifies rows in one table, and DELETE removes rows from one table.
You could easily fill a book with examples and explanations of these statements (in fact, many authors already have). This section covers the major parts of syntax and shows some basic examples. The SELECT StatementThe SELECT statement has the following basic syntax: SELECT [DISTINCT] [TOP N [PERCENT]] column1 [AS column_heading ] [, column2 [AS column_heading ], ...] [INTO new_table_name ] FROM table1 [ [AS] table_alias ] [ [INNER { LEFT RIGHT FULL} [OUTER] ] JOIN table2 [ [AS] table_alias2 ] on ( join_conditions )] [...] [WHERE search_conditions ] [GROUP BY aggregate_free_expression ] [HAVING search_condition ] [ORDER BY order_expression [ASC DESC]] [ compute row_aggregate ( column_name ) [, ... ] [ by column_name [ , column_name ] ... ] ] To return all the Utah authors' first and last names from the authors table and rename the column heading in your result, you would execute the following: SELECT au_lname AS 'First', au_fname AS 'Last' FROM authors WHERE STATE = 'UT' By default, SELECT returns all the rows that meet the search conditions. If you specify SELECT DISTINCT , any duplicate rows will be removed. Be careful not to use DISTINCT unnecessarily because it can slow query performance having to put the resultset into a work table to remove duplicates before returning the final resultset. The WHERE ClauseThe columns that you base your search condition on do not have to be returned in the resultset. You can filter rows in several ways with the WHERE clause. The following expressions are available for the WHERE clause:
Table 26.2. Wildcards and LIKE
Logical OR and AND are used to connect multiple search arguments. AND takes precedence over OR , so use parentheses to provide the proper logical grouping. For example, you might want to write a query to return all the business books for which the price is less than $10 or ytd_sales greater than $10,000. If you don't use parentheses, notice what happens when you run the following query: SELECT substring(title, 1, 30) as title, type, price, ytd_sales from titles where type = 'business' and price < or ytd_sales > 10000 go title type price ytd_sales --------------------------------- ------------- --------- --------- You Can Combat Computer Stress business 2.9900 18722 The Gourmet Microwave mod_cook 2.9900 22246 Fifty Years in Buckingham Pala trad_cook 11.9500 15096 You didn't get what you wanted. You only got business books with a price less than $20 and all books with ytd_sales greater than $10,000. Using parentheses, you get the result you want: SELECT substring(title, 1, 30) as title, type, price, ytd_sales from titles where type = 'business' and (price < or ytd_sales > 10000) go title type price ytd_sales ------------------------------- ----------- -------- ---------- You Can Combat Computer Stress business 2.9900 18722 The ORDER BY ClauseThe ORDER BY clause sorts the resultset by the specified column or columns. Ascending sorting is the default, but you can use ORDER BY column_name DESC to specify descending ordering. You should always specify ORDER BY if you require a certain order for your results. No specific ordering of resultsets are guaranteed otherwise , even when using DISTINCT or GROUP BY . The following is an example of a query using the ORDER BY clause to sort the resultset by au_lname : SELECT au_lname, au_fname, state FROM authors WHERE state IN('CA', 'KS') AND au_lname LIKE 'S%' ORDER BY au_lname As an alternative to specifying the column expression in the ORDER BY clause, you can specify the positional number of the expression in the select list or the column heading: SELECT au_lname AS 'Last', au_fname As 'First', state FROM authors WHERE state IN('CA', 'KS') AND au_lname LIKE 'S%' ORDER BY 1, First DESC The TOP KeywordYou can use the TOP keyword to restrict the number of rows returned. You can specify a fixed number of rows or a percentage of the number of rows in the resultset. The query shown in Listing 26.1 uses TOP along with ORDER BY to retrieve the title and price for the five most expensive books. Listing 26.1 Using TOP to Restrict the Number of Rows ReturnedSELECT TOP 5 price, title FROM titles ORDER BY price DESC go price title --------------------- -------------------------------------- 22.9500 But Is It User Friendly? 21.5900 Computer Phobic AND Non-Phobic Indi... 20.9500 Onions, Leeks, and Garlic: Cooking ... 20.0000 Secrets of Silicon Valley 19.9900 The Busy Executive's Database Guide
When using the TOP keyword, you can add the WITH TIES option to specify that additional rows should be returned from the resultset if duplicates are on the last values returned that are specified in an ORDER BY clause. The WITH TIES option can only be specified if an ORDER BY clause is specified. In Listing 26.2, you add WITH TIES so that all books that match the last price of the top five are returned. Listing 26.2 Using TOP WITH TIESSELECT TOP 5 WITH TIES price, title FROM titles ORDER BY price DESC go price title ------------- --------------------------------------------------------------- 22.9500 But Is It User Friendly? 21.5900 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 20.9500 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.0000 Secrets of Silicon Valley 19.9900 Prolonged Data Deprivation: Four Case Studies 19.9900 Silicon Valley Gastronomic Treats 19.9900 The Busy Executive's Database Guide 19.9900 Straight Talk About Computers If you don't use ORDER BY with TOP , the rows that will be returned cannot be predicted ”it is based on the execution plan chosen by the optimizer. You can also specify TOP n PERCENT to restrict the number of rows based on a percentage of the number of rows in the final resultset instead of specifying an absolute value.
SELECT INTOYou can create a table and store the resultset into it by using SELECT column(s) INTO table_name ... . The table specified will be created with the same columns and datatypes as columns in the resultset. Most SELECT options (aggregates, GROUP BY , ORDER BY ) are allowed except for the COMPUTE clause. If a column is a calculated expression such as an aggregate function or string concatenation, you will need to provide a column heading that can be used as a column name . (The column heading will have to conform to the rules for SQL Server identifiers.) If you precede the table name with one or two hash signs (#), a temporary table is created in tempdb. If you want to create a permanent table with SELECT...INTO , you must have create table permission in the destination database, and the database option select into/bulkcopy must be set to TRUE . The following example selects the type column and the average price from the titles table into a temporary table with the resultsets ordered in descending order by average price: select type, avg(price) AS avg_price into #type_avg_prices from titles group by type order by avg_price DESC UNIONWhen you use the UNION keyword, a logical union between two or more resultsets is returned. Each select list must have the same number of columns, and the corresponding columns must be of the same datatype or must allow implicit datatype conversions. The column headings specified in the first select list will be the column headings used for the entire resultset. This query returns the city and state of each author and publisher as a single resultset: SELECT city, state FROM authors UNION ALL SELECT city, state FROM publishers By default, SQL Server removes all the duplicate rows in a UNION . You can add the keyword ALL if you do not want the duplicates to be removed. Using the ALL keyword will speed up the return of the resultset because a work table is not required to remove the duplicate rows ”the results are simply appended to the previous resultset. To sort the resultset for a UNION , specify an ORDER BY clause in the last select statement in the union. The column names specified in the ORDER BY clause must match those in the first select statement in the union, or you can specify the column number. GROUP BY and HAVINGGROUP BY and HAVING are used with aggregate functions (which are described in the section "SQL Server Functions," later in this chapter). GROUP BY allows you to calculate aggregates for groups within your tables. The following example calculates the average price for each book category in the titles table: SELECT type, AVG(price) FROM titles GROUP BY type go type ---------------- ----------- business 13.7300 mod_cook 11.4900 popular_comp 21.4750 psychology 13.5040 trad_cook 15.9633 UNDECIDED NULL If a WHERE clause is used, it is applied before the grouping takes place. The following query calculates the average price per book category for books published by the publisher with the ID 1389: SELECT type, AVG(price) FROM titles WHERE pub_id = 1389 GROUP BY type go type ---------------- ----------- business 17.3100 popular_comp 21.4750 The HAVING lets you restrict the number of rows returned by filtering on the aggregate values calculated. The clause is applied after the grouping is applied and the aggregate values are determined. Perhaps you want to return the average price for book categories, but only the categories with an average that is higher than $14: SELECT type, AVG(price) FROM titles GROUP BY type HAVING AVG(price) > go type ---------------- ----------- popular_comp 21.4750 trad_cook 15.9633 CUBE , ROLLUP , and GROUPINGCUBE , ROLLUP , and GROUPING are used in conjunction with GROUP BY . ROLLUPThe ROLLUP operator provides aggregates and super-aggregates for elements within a GROUP BY statement. The ROLLUP operator can be used to extract running aggregates as well as cumulative aggregates within a resultset. The ROLLUP operator creates groupings from right to left, along the list of columns in the GROUP BY clause, applying the aggregate function to each grouping superset. A rollup value for a level displays NULL for that column, as shown in Listing 26.3. Listing 26.3 Using ROLLUP to Calculate Super-Aggregates select pub_id, type, t.title_id, sum(qty) as total_qty from titles t, sales s where t.title_id = s.title_id and type in ('business', 'popular_comp') group by pub_id, type, t.title_id with rollup go pub_id type title_id total_qty ------ ------------ -------- ----------- 0736 business BU2075 35 0736 business NULL 35 0736 NULL NULL 35 1389 business BU1032 15 1389 business BU1111 25 1389 business BU7832 15 1389 business NULL 55 1389 popular_comp PC1035 30 1389 popular_comp PC8888 50 1389 popular_comp NULL 80 1389 NULL NULL 135 NULL NULL NULL 170 Notice that you first get the aggregates rolled up by pub_id , type , and title_id , then by pub_id and type , then by pub_id , and finally a rollup for the entire resultset. CUBEThe CUBE operator is used with GROUP BY to generate a cross-referenced superset of groups to generate aggregates and super-aggregates. Unlike ROLLUP , which rolls up super-aggregate values from right to left, CUBE rolls up super-aggregates for every possible combination of the columns or expressions in the GROUP BY clause. The number of non-aggregate columns in the select list determines the number of groups in the resultset. If n columns or expressions exist, 2 ( n ) - 1 possible super-aggregate combinations will be present. If you add CUBE to the query that returns the average price for book categories, you get an extra row with the average price of all books, as shown in Listing 26.4. Listing 26.4 Using CUBE to Calculate Super-AggregatesSELECT type, AVG(price) AS average FROM titles GROUP BY type WITH CUBE go type average ------------ --------- business 13.7300 mod_cook 11.4900 popular_comp 21.4750 psychology 13.5040 trad_cook 15.9633 UNDECIDED NULL NULL 14.7662 The book type is returned as NULL for the extra row. CUBE is more useful if you group over several columns. In Listing 26.5, you want to return the average price grouped by book type and publisher. Listing 26.5 Grouping Over Several ColumnsSELECT type, pub_id, AVG(price) AS average FROM titles GROUP BY type, pub_id go type pub_id average ------------ ------ -------- business 0736 2.9900 psychology 0736 11.4825 mod_cook 0877 11.4900 psychology 0877 21.5900 trad_cook 0877 15.9633 UNDECIDED 0877 NULL business 1389 17.3100 popular_comp 1389 21.4750 In Listing 26.6, you add WITH CUBE , which provides the total average, the average for each book type, and the average for each publisher. Listing 26.6 Grouping Over Several Columns with CUBESELECT type, pub_id, AVG(price) AS average FROM titles GROUP BY type, pub_id WITH CUBE go type pub_id average ------------ --------- -------- business 0736 2.9900 business 1389 17.3100 business NULL 13.7300 mod_cook 0877 11.4900 mod_cook NULL 11.4900 popular_comp 1389 21.4750 popular_comp NULL 21.4750 psychology 0736 11.4825 psychology 0877 21.5900 psychology NULL 13.5040 trad_cook 0877 15.9633 trad_cook NULL 15.9633 UNDECIDED 0877 NULL UNDECIDED NULL NULL NULL NULL 14.7662 NULL 0736 9.7840 NULL 0877 15.4100 NULL 1389 18.9760 The GROUPING FunctionWhen working with the CUBE or ROLLUP operator, you can use the GROUPING function to distinguish between real null values and null values that represent a rollup of all values for a column in the resultset. The GROUPING function returns 1 when the value is grouped, and 0 when the column contains a null value. In Listing 26.7, you use the GROUPING function to replace null values for the rolled up columns with ALL . Listing 26.7 Using the GROUPING FunctionSELECT CASE when GROUPING(type) = 1 then 'ALL' else isnull(type, 'Other') END AS type, CASE when (grouping(pub_id) = 1) then 'ALL' else isnull(pub_id, 'Unknown') END as pub_id, AVG(price) AS average FROM titles GROUP BY type, pub_id WITH CUBE go type pub_id average ------------ ------ ------------ business 0736 2.9900 business 1389 17.3100 business ALL 13.7300 mod_cook 0877 11.4900 mod_cook ALL 11.4900 popular_comp 1389 21.4750 popular_comp ALL 21.4750 psychology 0736 11.4825 psychology 0877 21.5900 psychology ALL 13.5040 trad_cook 0877 15.9633 trad_cook ALL 15.9633 UNDECIDED 0877 NULL UNDECIDED ALL NULL ALL ALL 14.7662 ALL 0736 9.7840 ALL 0877 15.4100 ALL 1389 18.9760 You can also use the GROUPING function to order the resultsets to move all the rollups toward the bottom, as shown in Listing 26.8. Listing 26.8 Using the GROUPING Function to Order the ResultsetSELECT CASE when GROUPING(type) = 1 then 'ALL' else isnull(type, 'Unknown') END AS type, CASE when (grouping(pub_id) = 1) then 'ALL' else isnull(pub_id, 'Unknown') END as pub_id, AVG(price) AS average FROM titles GROUP BY type, pub_id WITH CUBE ORDER by GROUPING(type), GROUPING(pub_id) go type pub_id average ------------ ------ ---------- business 0736 2.9900 business 1389 17.3100 mod_cook 0877 11.4900 popular_comp 1389 21.4750 psychology 0736 11.4825 psychology 0877 21.5900 trad_cook 0877 15.9633 UNDECIDED 0877 NULL UNDECIDED ALL NULL trad_cook ALL 15.9633 psychology ALL 13.5040 popular_comp ALL 21.4750 mod_cook ALL 11.4900 business ALL 13.7300 ALL 0736 9.7840 ALL 0877 15.4100 ALL 1389 18.9760 ALL ALL 14.7662 Joining TablesTransact -SQL allows you to correlate data between two or more tables by performing a join. Generally, you connect the tables using a common column, which is most often a column for which a foreign key and primary key relationship has been specified. You can specify a join in two ways. First, you can specify the join condition in the WHERE clause. This is an older way of specifying a join, but it is still supported. Those of you who have been using SQL for a while are probably more familiar with this method. You can also specify the join condition in the FROM clause. This method complies with the ANSI-92 standard.
The following example shows both ways of expressing a join. Both statements return the same resultset: SELECT title, qty FROM titles t, sales s WHERE t.title_id = s.title_id SELECT title, qty FROM titles t INNER JOIN sales s ON t.title_id = s.title_id A table alias was also introduced in the example. The titles table was aliased to the name t and sales to s . Aliasing is useful when you have to refer to a table in several places in the query; you don't have to type the entire table name each time. Although defined in the FROM clause, a table alias can be used anywhere in the query that you have to qualify the column name. The different types of joins are INNER , OUTER , and CROSS . An INNER join is based on equality between the column values. Only rows with matching values on the join columns between the two tables will be returned in the resultset. The following example lists all authors and the average royalty paid to them for all matching records between authors and titleauthor: select au_lname, au_fname, avg(royaltyper) AS avg_royalty from authors a join titleauthor ta on a.au_id = ta.au_id group by au_lname, au_fname order by 3 go au_lname au_fname avg_royalty ---------------------------------------- -------------------- ----------- Gringlesby Burt 30 O'Leary Michael 35 Ringer Anne 37 Yokomoto Akiko 40 MacFeather Stearns 42 Hunter Sheryl 50 Dull Ann 50 Bennet Abraham 60 Green Marjorie 70 DeFrance Michel 75 Karsen Livia 75 Ringer Albert 75 Panteley Sylvia 100 Locksley Charlene 100 Carson Cheryl 100 Blotchet-Halls Reginald 100 del Castillo Innes 100 Straight Dean 100 White Johnson 100 The OUTER join returns all the rows from the specified outer table (specified with LEFT OUTER , RIGHT OUTER , or FULL OUTER ), even if the other table has no match. Rows returned from the outer table that have no corresponding match in the inner table will display the NULL symbol for any columns retrieved from the inner table. For example, you might want to display the names of all authors along with the average royalty paid if available: select au_lname, au_fname, avg(royaltyper) from authors a left outer join titleauthor ta on a.au_id = ta.au_id group by au_lname, au_fname order by 3 go au_lname au_fname avg_royalty ---------------------------------------- -------------------- ----------- Greene Morningstar NULL Greenfield Tom NULL McBadden Heather NULL Smith Meander NULL Stringer Dirk NULL Gringlesby Burt 30 O'Leary Michael 35 Ringer Anne 37 Yokomoto Akiko 40 MacFeather Stearns 42 Hunter Sheryl 50 Dull Ann 50 Bennet Abraham 60 Green Marjorie 70 DeFrance Michel 75 Karsen Livia 75 Ringer Albert 75 Panteley Sylvia 100 White Johnson 100 Straight Dean 100 Locksley Charlene 100 Carson Cheryl 100 Blotchet-Halls Reginald 100 del Castillo Innes 100 Note that no real difference exists between a left or right outer join except for specifying which table on which side of the join condition is to be the controlling, or outer table. For example, the previous query would provide the same result if you reversed tables in the join clause and made it a right outer join: select au_lname, au_fname, avg(royaltyper) from titleauthor ta right outer join authors a on ta.au_id = a.au_id group by au_lname, au_fname order by 3 A full outer join will return all matching rows from both tables along with all rows from each table without a corresponding match in the other table. A CROSS join returns all possible combinations of rows, also called a Cartesian product. Essentially , you are joining each row from one table with all the rows from another table. The total number of rows returned is the product of the number of rows in each table. Be careful using cross joins because the resultsets can become quite large, even for relatively small tables. For example, a cross join between a table with 1,000 rows and a table with 5,000 rows would return 1,000 * 5,000, or 5,000,000 rows. With the ANSI syntax, you specify the join type explicitly in the FROM clause, but the join type in the older join syntax is specified in the WHERE clause. The old style syntax allowed you to specify only a left outer join or a right outer join using the *= or =* operators, respectively. For example, the preceding outer join would be written as follows using the old style syntax: select au_lname, au_fname, avg(royaltyper) from authors a, titleauthor ta where a.au_id *= ta.au_id group by au_lname, au_fname order by 3 SubqueriesA subquery is essentially a query contained in another query. You can use a subquery in place of an expression. Depending on the context of the subquery, there might be restrictions on the data a subquery can return. Subqueries can only return a single column of data, and in some cases, they can only return a single row. If the subquery returns only one row and one column, it can be used in place of any expression. This example returns all books published by Binnet & Hardley: SELECT title FROM titles WHERE pub_id = (SELECT pub_id FROM publishers WHERE pub_name = 'Binnet & Hardley') An error message is returned if the subquery would have returned several rows. A subquery must always appear in parentheses.
You can use a subquery that returns one column and several rows with the IN predicate. The following example returns all publishers of business books: SELECT pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business') You can also use a subquery that returns several rows with the EXISTS keyword. The following example returns the same resultset as the preceding example: SELECT pub_name FROM publishers p WHERE EXISTS (SELECT * FROM titles t WHERE p.pub_id = t.pub_id AND type = 'business') Although the subquery specifies the * in the SELECT list, the subquery actually returns no column data. When the EXISTS keyword is used, the subquery returns a true or false condition depending on whether a matching record was found. This type of subquery is a correlated subquery . The inner query refers to a table in the outer query in the WHERE clause of the subquery ( WHERE p.pub_id = t.pub_id ). SQL Server executes the inner query for each row in the outer query, testing for a match on pub_id . After a match is found, SQL Server stops looking for additional matching rows and returns a true condition, adding the outer row to the resultset. Although this query could be represented as well with an IN predicate, you would have to use a correlated subquery like this when checking for the existence of matching rows between two tables where the common key is made up of more than one column. The IN predicate can only match against a single column in the subquery. A subquery can also be used in a column expression in query. For example, the following query substitutes the average price in place of price for any rows where price is null: select title, type, isnull(price, (select avg(price) from titles)) from titles Subqueries Versus JoinsSQL Server treats the subquery with the IN predicate or an EXISTS subquery in much the same way as a join, with one major exception. For example, following is a query using a join condition to return a resultset: select pub_name from publishers p, titles t where p.pub_id = t.pub_id and type = "business" go pub_name ---------------------------------------- New Age Books Algodata Infosystems Algodata Infosystems Algodata Infosystems Notice that duplicate rows are in the resultset. This is because the nature of a join is to find all matching rows. To remove the duplicates, you would need to specify the DISTINCT clause, which adds overhead of putting the rows into a work table to sort and remove the duplicate rows. With a large resultset, this could be expensive to do. The second method is to use the exists keyword to find only rows matching the condition, and to stop looking for results after the first match is found for a particular value. Following is the same query with an exists clause: select pub_name from publishers p where exists (select * from titles t where p.pub_id = t.pub_id and type = "business") go pub_name ---------------------------------------- New Age Books Algodata Infosystems
not exists and not inCertain results can be defined only with a subquery; for example, non-membership and non-existence can be expressed only in that way. For example, which publishers do not publish business books? This query cannot be expressed with a join, but it can be expressed using not exists or not in : select pub_name from publishers p where not exists (select * from titles t where t.pub_id = p.pub_id and type = 'business') select pub_name from publishers where pub_id not in (select pub_id from titles where type = 'business')
Subqueries with Aggregates in where ClausesOne specific case in which a subquery is required appears when you want to use an aggregate function in a where clause. The next example returns the type and price of all books whose price is below the average price. This condition in a where clause would be illegal: where price < avg(price) The server requires that the average price be derived in a subquery, as follows: select type, price from titles where price < (select avg(price) from titles) Subqueries as Derived TablesSQL Server provides the ability to use derived tables in your queries. A derived table is a subquery contained in a FROM clause that can be referred to by an alias and used as a table in the query. A derived table can be thought of as sort of a dynamic view that exists only for the duration of the query. Derived tables are handy if you don't need to use a resultset in a temp table more than once. One benefit of using a subquery as a derived table is that unlike a normal subquery, the derived table can return multiple columns that can be referenced by the outer query like normal table columns in the select list, where clause, and so on. The subquery can contain aggregates, group by, UNION , and so on if needed. For example, perhaps you need to write a query to return the average, by publisher, of the largest sale for each book. SQL Server doesn't allow an aggregate within an aggregate, so a query like the following wouldn't work: select pub_name, sum(max(qty)) as top_sales from sales s join titles t on s.title_id = t.title_id join publishers p on t.pub_id = p.pub_id group by title_id, pub_name You could break it up into two queries, putting intermediate results of the max(qty) from the titles and sales table into a temporary table, and then run a query against the temp table to calculate the avg of the max(qty). You could also write a more elegant solution using a derived table: select pub_name, avg(top_sales) as avg_top_sales from publishers p join titles t on p.pub_id = t.pub_id join (select title_id, max(qty) as top_sales from sales group by title_id) as tot_sales on t.title_id = tot_sales.title_id group by pub_name Adding Rows with INSERTYou use the INSERT statement to add rows to a table. The following example adds one row to the authors table: INSERT authors (au_id, au_lname, au_fname, phone, contract) VALUES('123-65-7635', 'Johnson', 'Lisa', '408 342 7845', 1) The number of values in the VALUES list must match the number in the column list. You can omit the column list only if you are providing values for all columns in the table, except for any identity column that might exist on the table. It is strongly recommended that you always provide a column list in your insert statement so that the mapping of values to columns is explicitly defined. Without a column list, your INSERT statement depends on the column order not changing or no columns being added or removed form the table. If the number of columns in the table no longer matches the number of values specified, the INSERT will fail with the following error message: Server: Msg 213, Level 16, State 4, Line 1 Insert Error: Column name or number of supplied values does not match table definition. If you want to omit table columns in your insert statement, you must provide a column list. In addition, you can only omit column(s) from the insert statement if the columns allow NULL , have a default value associated with them, are of the timestamp datatype, or have the identity property defined for them. You cannot insert more than one row at a time using the VALUES clause. If you need to add multiple rows, you will need to specify a separate insert statement for each row. The only time an insert statement can insert more than one row is when the INSERT uses a SELECT statement in place of the values clause. The following query creates a table called authors_archive and inserts all authors from California into it: if object_id('authors_archive') is null -- create the archive table create table authors_archive (au_id id NOT NULL , au_lname varchar 40) NOT NULL , au_fname varchar(20) NOT NULL , phone char(12) NOT NULL, address varchar(40) NULL , city varchar(20) NULL , state char(2) NULL , zip char(5) NULL) go INSERT authors_archive (au_id, au_lname, au_fname, phone, city, state, zip) SELECT au_id, au_lname, au_fname, phone, city, state, zip FROM authors WHERE state = 'CA' go Another useful feature is the ability to insert data from the resultset(s) of a stored procedure, user-defined function, or dynamic query. This can be done as long as the stored procedure, user-defined function, or dynamic query returns one or more resultsets that are compatible with the destination table structure. To be compatible, the resultset(s) must contain the same number of columns as the destination table, and the datatypes of the resultset columns must be compatible with the corresponding datatype columns. Listing 26.9 creates a table to hold information from DBCC SQLPERF(logspace) and inserts the resultset returned by that command into the table. Listing 26.9 Using INSERT with the Resultset from a Dynamic Query CREATE TABLE log_space (cap_date DATETIME DEFAULT GETDATE(), db sysname, log_size FLOAT, space_used FLOAT, status BIT) INSERT log_space(db, log_size, space_used, status) EXEC ('DBCC SQLPERF(logspace)') select * from log_space go cap_date db log_size space_used status ------------------------ --------- --------- ------------------ ------ 2002-08-24 18:13:37.010 master 3.3671875 29.698375701904297 0 2002-08-24 18:13:37.010 tempdb 0.4921875 39.186508178710938 0 2002-08-24 18:13:37.010 model 0.4921875 48.809524536132813 0 2002-08-24 18:13:37.010 msdb 2.2421875 32.186412811279297 0 2002-08-24 18:13:37.010 pubs 0.7421875 62.105262756347656 0 2002-08-24 18:13:37.010 Northwind 0.9921875 48.868110656738281 0 2002-08-24 18:13:37.010 bigpubs 0.484375 43.850807189941406 0 Modifying Rows with UPDATEThe update statement is straightforward. You specify the table to be updated, the columns to modify, the new values, and the rows to be updated. The following statement changes the royalty to 15% and price to $25 for a book with a title_id of 'BU1032' : UPDATE titles SET royalty = 15, price = WHERE title_id = 'BU1032' If you omit the WHERE clause, all rows will be updated. You can use a join in an UPDATE statement (this is a T-SQL extension) to qualify the rows in a table to be modified: /* increase prices by 5 times ** where the author's last name is Smythe */ update titles set price = price * 5 from authors a join titleauthor ta on (a.au_id = ta.au_id ) join titles t on (t.title_id = ta.title_id ) where au_lname = 'White' Notice that the titles table is specified twice in the update statement, both in the UPDATE and FROM clauses. The table name is required in the UPDATE statement to specify which table is to be modified, and again in the FROM clause to specify the join. A subquery can provide a similar result as a join: update titles set advance = advance * 1.5 where pub_id in (select pub_id from publishers where state = 'CA') You can also use a correlated subquery in an update. The following type of query is common for generating/refreshing a rollup quantity in a separate table: update titles set ytd_sales = (select sum(qty) from sales s where titles.title_id = s.title_id and datediff(yy, ord_date, getdate()) = 0) Removing Rows with DELETETo remove rows from a table, use the DELETE statement. To remove the rows with an author last name of "Smith", type the following: DELETE authors WHERE au_lname = 'Smith' If you omit the WHERE clause, all rows are removed. If you really want to remove all rows, it is much more efficient to use the TRUNCATE TABLE statement, which does not log each deleted row to the transaction log. T-SQL also allows for a join to be used in a delete statement to qualify the rows in a table to be deleted: /* remove titleauthor where author last name ** is 'White' */ delete titleauthor from titleauthor t join authors a on ( a.au_id = t.au_id) where au_lname = 'White' A subquery will work similar to a join: /* delete titleauthor records for authors with last name "Ringer" */ delete titleauthor where au_id in (select au_id from authors where au_lname = 'Ringer')  |
EAN: 2147483647
Pages: 503