Transcript No Slide Title

Chapter 7
How to insert, update,
and delete data
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 1
Objectives
Applied
 Given the specifications for an action query, code the INSERT, UPDATE,
or DELETE statement for doing the action.
 Use the MERGE statement to merge rows from a source table into a target
table.
 Create a copy of a table by using the INTO clause of the SELECT statement.
Knowledge
 Describe the three types of action queries.
 Explain how to handle null values and default values when coding INSERT
and UPDATE statements.
 Explain how the FROM clause is used in an UPDATE or DELETE
statement.
 Explain how the MERGE statement works
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 2
The syntax of the SELECT INTO statement
SELECT select_list
INTO table_name
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_list]
[HAVING search_condition]
[ORDER BY order_by_list]
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 3
How to use the SELECT INTO statement to create
test tables
 The INTO clause is a SQL Server extension that lets you create a
new table based on the result set of the SELECT statement.
 The definitions of the columns in the new table are based on the
columns in the result set.
 If you use a calculated value in the select list, you must name the
column since that name is used in the definition of the new table.
 Only column definitions and data are copied to the new table. The
definitions of keys, indexes, default values, and so on, aren’t
included.
Note
 The table you name in the INTO clause must not exist. If it does,
you must delete it using the DROP TABLE statement before you
execute the SELECT INTO statement.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 4
A statement that creates a complete copy of the
Invoices table
SELECT *
INTO InvoiceCopy
FROM Invoices
(114 row(s) affected)
A statement that creates a partial copy of the
Invoices table
SELECT *
INTO OldInvoices
FROM Invoices
WHERE InvoiceTotal – PaymentTotal – CreditTotal = 0
(103 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 5
A statement that creates a table with summary
rows from the Invoices table
SELECT VendorID, SUM(InvoiceTotal) AS SumOfInvoices
INTO VendorBalances
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal <> 0
GROUP BY VendorID
(7 row(s) affected)
A statement that deletes a table
DROP TABLE InvoiceCopy
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 6
The syntax of the INSERT statement
INSERT [INTO] table_name [(column_list)]
[DEFAULT] VALUES (expression_1 [, expression_2]...)
[, (expression_1 [, expression_2]...) ...]
How to insert a single row into a table
 You use the INSERT statement to add a new row to a table.
 In the INSERT clause, you specify the name of the table that you
want to add a row to, along with an optional column list.
 You specify the values to be inserted in the VALUES clause. The
values you specify depend on whether you include a column list.
 If you don’t include a column list: You must code a value for every
column, in the same order as they appear in the table. The only
exception is an identity column, which must be omitted.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 7
How to insert a single row into a table (continued)
 If you include a column list: You must specify the column values in
the same order as they appear in the column list. You can omit
columns with default values and columns that accept null values,
and you must omit identity columns.
 To insert a null value into a column, you can use the NULL
keyword.
 To insert a default value into a column, you can use the DEFAULT
keyword.
 If all of the table columns are identity columns, columns with
default values, or columns that allow null values, you can code the
DEFAULT keyword at the beginning of the VALUES clause and
then omit the list of values.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 8
The values for a new row in the Invoices table
Column
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
Murach’s SQL Server 2005, C7
Value
(Next available
unique ID)
97
456789
8/01/2008
8,344.50
Column
PaymentTotal
Value
0
CreditTotal
TermsID
InvoiceDueDate
PaymentDate
0
1
8/31/2008
null
© 2007, Mike Murach & Associates, Inc.
Slide 9
An INSERT statement that adds the new row
without using a column list
INSERT INTO InvoiceCopy
VALUES (97, '456789', '2008-08-01', 8344.50, 0, 0, 1,
'2008-08-31', NULL)
An INSERT statement that adds the new row using
a column list
INSERT INTO InvoiceCopy
(VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal,
CreditTotal, TermsID, InvoiceDate, InvoiceDueDate)
VALUES (97, '456789', 8344.50, 0, 0, 1, '2008-08-01',
'2008-08-31')
The response from the system
(1 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 10
An INSERT statement that adds three new rows
INSERT INTO InvoiceCopy
VALUES
(95, '111-10098', '2008-08-01', 219.50, 0, 0, 1,
'2008-08-31', NULL),
(102, '109596', '2008-08-01', 22.97, 0, 0, 1,
'2008-08-31', NULL),
(72, '40319', '2008-08-01', 173.38, 0, 0, 1,
'2008-08-31', NULL)
The response from the system
(3 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 11
The definition of the ColorSample table
Column
name
ID
ColorNumber
ColorName
Data
type
Int
Int
VarChar
Murach’s SQL Server 2005, C7
Length
4
4
10
Identity
Yes
No
No
© 2007, Mike Murach & Associates, Inc.
Allow
nulls
No
No
Yes
Default
value
No
0
No
Slide 12
Six INSERT statements for the ColorSample
table
INSERT INTO ColorSample (ColorNumber)
VALUES (606)
INSERT INTO ColorSample (ColorName)
VALUES ('Yellow')
INSERT INTO ColorSample
VALUES (DEFAULT, 'Orange')
INSERT INTO ColorSample
VALUES (808, NULL)
INSERT INTO ColorSample
VALUES (DEFAULT, NULL)
INSERT INTO ColorSample
DEFAULT VALUES
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 13
The ColorSample table after the rows are inserted
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 14
The syntax of the INSERT statement for inserting
rows selected from another table
INSERT [INTO] table_name [(column_list)]
SELECT column_list
FROM table_source
[WHERE search_condition]
How to insert rows selected from other tables
 If you code a subquery in place of the VALUES clause, the rows
in the derived table are inserted into the INSERT table.
 The subquery must return values for required columns in the
correct order the same way the VALUES clause does.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 15
An INSERT statement that inserts paid invoices in
the InvoiceCopy table into the InvoiceArchive table
INSERT INTO InvoiceArchive
SELECT *
FROM InvoiceCopy
WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0
(103 row(s) affected)
The same INSERT statement with a column list
INSERT INTO InvoiceArchive
(InvoiceID, VendorID, InvoiceNumber, InvoiceTotal,
CreditTotal, PaymentTotal, TermsID, InvoiceDate,
InvoiceDueDate)
SELECT
InvoiceID, VendorID, InvoiceNumber, InvoiceTotal,
CreditTotal, PaymentTotal, TermsID, InvoiceDate,
InvoiceDueDate
FROM InvoiceCopy
WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0
(103 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 16
The syntax of the UPDATE statement
UPDATE table_name
SET column_name_1 = expression_1
[, column_name_2 = expression_2]...
[FROM table_source [[AS] table_alias]
[WHERE search_condition]
How to perform a basic update operation
 You use the UPDATE statement to modify one or more rows in
the table named in the UPDATE clause.
 You name the columns to be modified and the value to be assigned
to each column in the SET clause.
 You can specify the value for a column by using a literal, an
expression, or the DEFAULT or NULL keyword when
appropriate.
 Warning: If you omit the WHERE clause, all the rows in the table
will be updated.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 17
An UPDATE statement that assigns new values to
two columns of one row in the InvoiceCopy table
UPDATE InvoiceCopy
SET PaymentDate = '2008-09-21', PaymentTotal = 19351.18
WHERE InvoiceNumber = '97/522'
(1 row(s) affected)
An UPDATE statement that assigns a new value to
one column of all the invoices for a vendor
UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID = 95
(6 row(s) affected)
An UPDATE statement that uses an arithmetic
expression to assign a value to a column
UPDATE InvoiceCopy
SET CreditTotal = CreditTotal + 100
WHERE InvoiceNumber = '97/522'
(1 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 18
How to use subqueries in an UPDATE statement
 You can use a subquery in the SET clause to return the value that’s
assigned to a column.
 You can use a subquery in the FROM clause to identify the rows
that are available for update. Then, you can refer to the derived
table in the SET and WHERE clauses.
 You can use a subquery in the WHERE clause to provide one or
more values used in the search condition.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 19
An UPDATE statement that assigns the maximum
due date in the InvoiceCopy table to an invoice
UPDATE InvoiceCopy
SET CreditTotal = CreditTotal + 100,
InvoiceDueDate =
(SELECT MAX(InvoiceDueDate) FROM InvoiceCopy)
WHERE InvoiceNumber = '97/522'
(1 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 20
An UPDATE statement that updates all the
invoices for a vendor based on the vendor’s name
UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID =
(SELECT VendorID
FROM VendorCopy
WHERE VendorName = 'Pacific Bell')
(6 row(s) affected)
An UPDATE statement that changes the terms of
all invoices for vendors in three states
UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID IN
(SELECT VendorID
FROM VendorCopy
WHERE VendorState IN ('CA', 'AZ', 'NV'))
(51 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 21
An UPDATE statement that applies a $100 credit to
the 10 invoices with the largest balances of $100 or
more
UPDATE InvoiceCopy
SET CreditTotal = CreditTotal + 100
FROM
(SELECT TOP 10 InvoiceID
FROM InvoiceCopy
WHERE InvoiceTotal - PaymentTotal - CreditTotal
>= 100
ORDER BY InvoiceTotal – PaymentTotal - CreditTotal
DESC)
AS TopInvoices
WHERE InvoiceCopy.InvoiceID = TopInvoices.InvoiceID
(5 rows(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 22
How to use joins in an update operation
 If you need to specify column values or search conditions that
depend on data in a table other than the one named in the
UPDATE clause, you can use a join in the FROM clause.
 You can use columns from the joined tables in the values that you
assign to columns in the SET clause or in the search condition of a
WHERE clause.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 23
An UPDATE statement that changes the terms of
all the invoices for a vendor
UPDATE InvoiceCopy
SET TermsID = 1
FROM InvoiceCopy JOIN VendorCopy
ON InvoiceCopy.VendorID = VendorCopy.VendorID
WHERE VendorName = 'Pacific Bell'
(6 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 24
An UPDATE statement that updates contact
names in the VendorCopy table based on data
in the ContactUpdates table
UPDATE VendorCopy
SET VendorContactLName = LastName,
VendorContactFName = FirstName
FROM VendorCopy JOIN ContactUpdates
ON VendorCopy.VendorID = ContactUpdates.VendorID
(8 row(s) affected)
The ContactUpdates table
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 25
The syntax of the DELETE statement
DELETE [FROM] table_name
[FROM table_source]
[WHERE search_condition]
How to perform a basic delete operation
 You can use the DELETE statement to delete one or more rows
from the table you name in the DELETE clause.
 You specify the conditions that must be met for a row to be deleted
in the WHERE clause.
 You can specify additional criteria for the delete operation in the
FROM clause.
 Warning: If you omit the WHERE clause, all the rows in the table
will be deleted.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 26
A DELETE statement that removes a single row
from the InvoiceCopy table
DELETE InvoiceCopy
WHERE InvoiceID = 115
(1 row(s) affected)
A DELETE statement that removes all the invoices
for a vendor
DELETE InvoiceCopy
WHERE VendorID = 37
(3 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 27
A DELETE statement that removes all paid
invoices
DELETE InvoiceCopy
WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0
(103 row(s) affected)
A DELETE statement that removes all the rows
from the InvoiceCopy table
DELETE InvoiceCopy
(114 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 28
How to use subqueries and joins in a DELETE
statement
 You can use subqueries and joins in the FROM clause to base the
delete operation on the data in tables other than the one named in
the DELETE clause.
 You can use any of the columns returned by a subquery or a join
in the WHERE clause.
 You can also use subqueries in the WHERE clause to provide one
or more values used in the search condition.
Note
 The FROM clause is a SQL Server extension.
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 29
A DELETE statement that deletes all invoices for a
vendor based on the vendor’s name
DELETE InvoiceCopy
WHERE VendorID =
(SELECT VendorID
FROM VendorCopy
WHERE VendorName = 'Blue Cross')
(3 row(s) affected)
The same DELETE statement using a join
DELETE InvoiceCopy
FROM InvoiceCopy JOIN VendorCopy
ON InvoiceCopy.VendorID = VendorCopy.VendorID
WHERE VendorName = 'Blue Cross'
(3 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 30
A DELETE statement that deletes vendors that
don’t have invoices
DELETE VendorCopy
WHERE VendorID NOT IN
(SELECT DISTINCT VendorID FROM InvoiceCopy)
(88 row(s) affected)
A DELETE statement that deletes vendors whose
invoices total $100 or less
DELETE VendorCopy
FROM VendorCopy JOIN
(SELECT VendorID, SUM(InvoiceTotal)
AS TotalOfInvoices
FROM InvoiceCopy
GROUP BY VendorID) AS InvoiceSum
ON VendorCopy.VendorID = InvoiceSum.VendorID
WHERE TotalOfInvoices <= 100
(6 row(s) affected)
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 31
The syntax of the MERGE statement (SQL Server
2008)
MERGE [INTO] table_target
USING table_source
ON join_condition
[WHEN MATCHED [AND search_condition]...
THEN dml_statement ]
[WHEN NOT MATCHED [BY TARGET ][AND search_condition]...
THEN dml_statement ]
[WHEN NOT MATCHED BY SOURCE [AND search_condition]...
THEN dml_statement ]
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 32
A MERGE statement
MERGE InvoiceArchive AS ia
USING InvoiceCopy AS ic
ON ic.InvoiceID = ia.InvoiceID
WHEN MATCHED AND
ic.PaymentDate IS NOT NULL AND
ic.PaymentTotal > ia.PaymentTotal
THEN
UPDATE SET
ia.PaymentTotal = ic.PaymentTotal,
ia.CreditTotal = ic.CreditTotal,
ia.PaymentDate = ic.PaymentDate
WHEN NOT MATCHED THEN
INSERT (InvoiceID, VendorID, InvoiceNumber,
InvoiceTotal, PaymentTotal, CreditTotal,
TermsID, InvoiceDate, InvoiceDueDate)
VALUES (ic.InvoiceID, ic.VendorID, ic.InvoiceNumber,
ic.InvoiceTotal, ic.PaymentTotal, ic.CreditTotal,
ic.TermsID, ic.InvoiceDate, ic.InvoiceDueDate)
;
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 33
A WHEN clause that deletes rows that aren’t
matched by the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE
Murach’s SQL Server 2005, C7
© 2007, Mike Murach & Associates, Inc.
Slide 34