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

SQL Workshop  Insert, Update, Delete Doug Shook

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 2

SELECT INTO 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)  These statements create new tables 3

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 4

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 5

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

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

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 8

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 0 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; 9

Default and null values The ColorSample table after the rows are inserted 10

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. 11

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

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 13

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

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 15

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

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

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

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

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 20

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? 21

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

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

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

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

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 26

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

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 28

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. 29