SQL Training Insert Update Delete Insert Update Delete

SQL Training Insert, Update & Delete

Insert, Update, Delete

Manipulating Data Update Insert Database Delete You must remember the referential integrity rules when you manipulate data in the database. Referential Integrity • • “Thou shall not create orphans. ” Every child (foreign key) must have a matching parent (primary key). You can not delete a parent if there is a matching child. You can not add a child record if you do not have a matching parent record. Page 3 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Insert Add a new vendor: Approved to supply component: Vendor. Name: New 2 You Address: 9983 Buy Now Ave Miami FL 32940 Contact: Tracy Knew Phone: 321 -987 -1001 Fax: 321 -987 -1000 Email: tknew@new 2 you. com FL Province. ID = 9 Component. ID: 14 Vendor Code: N 2 U 9870 -C Vendor Price: $45. 98 Referential Integrity Rules require which table to be populated first? How are we going to determine the value for the vendorid? Page 4 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Insert 1. You can assign unique numbers, such as ID’s, to columns in your database using a sequence. Create Sequence Vendor. ID increment by 1 start with 1; We will assume that the next available vendorid is 102 2. We need to populate the data for the parent table (Vendor) first. Insert into Vendor (Vendor. ID, Vendor. Name, Address 1, City, Province. ID, Phone, Fax, Email, Vendor. First. Name, Vendor. Last. Name) Values (102, 'New 2 You', '9983 Buy Now Ave', 'Melbourne', 9, '321 -987 -1001', '321 -987 -1000', 'tknew@new 2 you. com', 'Tracy', 'Knew'); 3. After each update to the database you need to commit. If you forget to commit, the changes will be rolled-back when you exit SQL Plus (the default action). Commit; Page 5 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Insert 4. Next, we need to insert the data into the child (Vendor. Component) table. Insert into Vendor. Component (Vendor. ID, Component. ID, Vendor. Part. Number, Vendor. Price) Values ((Select max(Vendor. ID) from Vendor), 14, ‘N 2 U 9870 -C’, 45. 98); We have to figure out what Vendor. ID was assigned in the parent table. 5. Again, we will need to commit to make the changes permanent in the database. Commit; Page 6 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Insert – Select CREATE TABLE French. Vendor ( Vendor. ID Vendor. Name Province. ID Address 1 City Postal. Code ); INTEGER, VARCHAR 2(30), VARCHAR 2(20), VARCHAR 2(15) INSERT INTO French. Vendor (Vendor. ID, Vendor. Name, Province. ID, Address 1, City, Postal. Code) SELECT Vendor. ID, Vendor. Name, Province. ID, Address 1, City, Postal. Code FROM Vendor, Province, Country, Region WHERE Vendor. Province. ID = Province. ID and Region. Country. ID = Country. ID and Province. Region. ID = Region. ID and Country. Name = 'France'; Page 7 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Update the conversion rate of Canada to. 00911. UPDATE Country SET Currency. Rate =. 00911, Currency. Date = sysdate WHERE Country. ID = 2; 1 row updated. Updates the column values within one or more rows of a table. Page 8 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Update Multiple Rows Increase the unit price of all Butane Homegens by 2%. UPDATE Product SET Product. Price = Product. Price * 1. 02 WHERE Product. Description Like '%Butane%' 21 rows updated. Page 9 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Delete Product. Code HG 5000 -01 B from the Purchase. Order table. DELETE FROM Product WHERE Product. Code = ‘HG 5000 -01 B’; ERROR at line 1: ORA-02292: integrity constraint (Oracle. SYS_C 0042860) violated - child record found Why do we get this error? Note: The WHERE clause determines the rows that are deleted. If the WHERE clause is not specified, all rows are deleted (subject to referential integrity constraints). Page 10 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Delete Multiple Rows Delete Product. Code HG 5000 -01 B from the Product table. Step 1: Delete the child rows from the Manifest Table. DELETE FROM Manifest WHERE Product. ID = (SELECT Product. ID FROM Product WHERE Product. Code = 'HG 5000 -01 B'); Step 2: Delete parent record from the Purchase. Order Table. DELETE FROM Product WHERE Product. Code = 'HG 5000 -01 B'; Step 3: Commit If you forget to commit after you have updated the database your changes will be rolled back (undo) as soon as you exit SQL Plus. Page 11 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Workshop

Insert Add a new Survey record: Column Value Surveyid 803 Customerid 4 Userid 7 Requiredproductid 54 surveydate 2/8/2010 Q 1 5 Q 2 6 Q 3 7 Q 4 3 Q 5 4 Q 6 8 Q 7 9 Page 13 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Update Change the values below for survey is 803. Column Value Surveyid 803 Customerid Userid Requiredproductid surveydate Q 1 4 Q 2 Q 3 6 Q 4 Q 5 8 Q 6 Q 7 2 Page 14 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.

Delete survey 803. Column Value Surveyid 803 Customerid Userid Requiredproductid surveydate Q 1 4 Q 2 Q 3 6 Q 4 Q 5 8 Q 6 Q 7 2 Page 15 • Confidential & Proprietary Copyright © 2009 Cardinal Directions, Inc.