SQL Workshop Insert, Update, Delete Doug Shook Test Tables So far - - PowerPoint PPT Presentation

SQL Workshop

Insert, Update, Delete

Doug Shook

2

Test Tables

 So far we have only read data  When testing code that may change data, it is

important to NEVER use the production DB – Run the statements on test tables before deploying

 There are two ways to accomplish this

– Create a copy of the database – Use the INTO clause

3

SELECT INTO

 These statements create new tables

Create a complete copy of the Invoices table

SELECT * INTO InvoiceCopy FROM Invoices; (114 row(s) affected)

Create a partial copy of the Invoices table

SELECT * INTO OldInvoices FROM Invoices WHERE InvoiceTotal – PaymentTotal – CreditTotal = 0; (103 row(s) affected)

4

Test Tables

 Things to watch out for:

– Only column definitions and data are copied – Primary keys, indexes, default values, etc. are not included in the new table – If calculated values are used, you must name the column

 To delete a table you can use the DROP TABLE

statement

5

INSERT

 Creates a new row within a table  Two methods:

– Column list – Ordered values

 Identity columns should be left off

– Generated by SQL Server – More on these later

6

INSERT

Insert the row without using a column list

INSERT INTO InvoiceCopy VALUES (97, '456789', '2012-04-01', 8344.50, 0, 0, 1, '2012-04-30', NULL);

Insert the row using a column list

INSERT INTO InvoiceCopy (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2012-04-01', '2012-04-30');

The response from the system

(1 row(s) affected)

7

INSERT

Insert three rows

INSERT INTO InvoiceCopy VALUES (95, '111-10098', '2012-04-01', 219.50, 0, 0, 1, '2012-04-30', NULL), (102, '109596', '2012-04-01', 22.97, 0, 0, 1, '2012-04-30', NULL), (72, '40319', '2012-04-01', 173.38, 0, 0, 1, '2012-04-30', NULL);

The response from the system

(3 row(s) affected)

8

Default and null values

 If a column accepts null values, simply use NULL if

desired

 If a column accepts default values, use DEFAULT  Columns that are omitted from VALUES will use

NULL or DEFAULT as necessary

9

Default and null values

The definition of the ColorSample table

Column Data Allow Default Name Type Length Identity Nulls Value ID Int 4 Yes No No ColorNumber Int 4 No No ColorName VarChar 10 No Yes No

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;

10

Default and null values

The ColorSample table after the rows are inserted

11

Using SELECT to insert rows

 A subquery can be used in place of VALUES

– All rows from derived table are inserted

 Have to be careful:

– Columns must match datatypes, position, etc.

12

Using SELECT to insert rows

Insert paid invoices 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)

13

UPDATE

 Used to modify one or more rows  Two important clauses

– SET

• Used to specify column/value pairs

– WHERE

• Used to specify which rows to modify
• If omitted, all rows are updated

14

UPDATE

Update two columns of a single row

UPDATE InvoiceCopy SET PaymentDate = '2012-05-21', PaymentTotal = 19351.18 WHERE InvoiceNumber = '97/522'; (1 row(s) affected)

Update one column of multiple rows

UPDATE InvoiceCopy SET TermsID = 1 WHERE VendorID = 95; (6 row(s) affected)

Update a column using an arithmetic expression

UPDATE InvoiceCopy SET CreditTotal = CreditTotal + 100 WHERE InvoiceNumber = '97/522'; (1 row(s) affected)

15

UPDATE with subqueries

 Can be used with SET, FROM, and WHERE

– SET

• Returns a value that’s assigned to a column

– FROM

• Identify rows that are available for updating
• Use derived table in SET or WHERE clauses

– WHERE

• Provides one or more values for search

condition

16

UPDATE with subqueries

A subquery that returns the value assigned to a column

UPDATE InvoiceCopy SET CreditTotal = CreditTotal + 100, InvoiceDueDate = (SELECT MAX(InvoiceDueDate) FROM InvoiceCopy) WHERE InvoiceNumber = '97/522'; (1 row(s) affected)

17

UPDATE with subqueries

A subquery used in a search condition

UPDATE InvoiceCopy SET TermsID = 1 WHERE VendorID = (SELECT VendorID FROM VendorCopy WHERE VendorName = 'Pacific Bell'); (6 row(s) affected)

Another subquery used in a search condition

UPDATE InvoiceCopy SET TermsID = 1 WHERE VendorID IN (SELECT VendorID FROM VendorCopy WHERE VendorState IN ('CA', 'AZ', 'NV')); (51 row(s) affected)

18

UPDATE with subqueries

A subquery that identifies the rows available for update

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)

19

UPDATE with joins

 Joins can be used with FROM to pull values from

multiple tables:

A column in a joined table used in a WHERE clause

UPDATE InvoiceCopy SET TermsID = 1 FROM InvoiceCopy JOIN VendorCopy ON InvoiceCopy.VendorID = VendorCopy.VendorID WHERE VendorName = 'Pacific Bell'; (6 row(s) affected)

20

UPDATE with joins

Columns in a joined table used in a SET clause

UPDATE VendorCopy SET VendorContactLName = LastName, VendorContactFName = FirstName FROM VendorCopy JOIN ContactUpdates ON VendorCopy.VendorID = ContactUpdates.VendorID; (8 row(s) affected)

The ContactUpdates table

21

DELETE

 Used to remove rows from a table

– Specify conditions in the WHERE clause

 Omitting WHERE will delete all rows

– How is this different from DROP TABLE?

22

DELETE

The syntax of the DELETE statement

DELETE [FROM] table_name [FROM table_source] [WHERE search_condition]

Delete a single row from the InvoiceCopy table

DELETE InvoiceCopy WHERE InvoiceID = 115; (1 row(s) affected)

Delete all the invoices for a vendor

DELETE InvoiceCopy WHERE VendorID = 37; (3 row(s) affected)

23

DELETE

Delete all paid invoices

DELETE InvoiceCopy WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0; (103 row(s) affected)

Delete all the rows

DELETE InvoiceCopy; (114 row(s) affected)

24

DELETE with subqueries and joins

 Typically used with FROM or WHERE

– Provides data from other tables to use as conditions

A subquery used in a search condition

DELETE InvoiceCopy WHERE VendorID = (SELECT VendorID FROM VendorCopy WHERE VendorName = 'Blue Cross'); (3 row(s) affected)

The same statement using a join

DELETE InvoiceCopy FROM InvoiceCopy JOIN VendorCopy ON InvoiceCopy.VendorID = VendorCopy.VendorID WHERE VendorName = 'Blue Cross'; (3 row(s) affected)

25

DELETE

Another subquery used in a search condition

DELETE VendorCopy WHERE VendorID NOT IN (SELECT DISTINCT VendorID FROM InvoiceCopy); (88 row(s) affected)

A derived table joined with another table

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)

26

MERGE

 Used to merge multiple rows from one table into

another table – Referred to as “upsertting”

 Optional WHEN clause

– Controls when and how a row is inserted, updated, or deleted – Can include INSERT UPDATE or DELETE statements

27

MERGE

Insert and update rows

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) ;

28

Exercises

 Write an INSERT statement that adds a row to the

InvoiceCopy table with the following values: VendorID: 32 InvoiceTotal: $434.58 TermsID: 2 InvoiceNumber: AX-014-027 PaymentTotal: $0.00 InvoiceDueDate: 07/08/12 InvoiceDate: 06/21/12 CreditTotal: $0.00 PaymentDate: null

29

Exercises

 Write an INSERT statement that adds a row to the

VendorCopy table for each non-California vendor in the Vendors table.

 Write an UPDATE statement that modifies the

VendorCopy table. Change the default account number to 403 for each vendor that has a default account number of 400.

30

Exercises

 Write an UPDATE statement that modifies the

InvoiceCopy table. Change the PaymentDate to today's date, and the PaymentTotal to the balance doue for each invoice with a balance due. Set today's date with a literal string. Write and UPDATE statement that modifies the InvoiceCopy table. Change TermsID to 2 for each invoice that's from a vendor with a DefaultTermsID of

• 2. Use a subquery.

31

Exercises

 Redo the previous exercise with a join instead of a

subquery.

 Write a DELETE statement that deletes all vendors in

the state of Minnesota from the VendorCopy table.

 Write a DELETE statement for the VendorCopy table.

Delete the vendors that are located in states from which no vendor has ever sent an invoice. Hint: Use a subquery coded with "SELECT DISTINCT VendorState" introduced with the NOT IN operator.