SQL Training Insert, Update & Delete

1. SQL Training Insert, Update & Delete

2. Insert, Update, Delete

3. Manipulating Data Update Delete Database Insert 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.

4. Insert Add a new vendor: VendorName: New2You Address: 9983 Buy Now Ave Miami FL 32940 Contact: Tracy Knew Phone: 321-987-1001 Fax: 321-987-1000 Email: tknew@new2you.com FL  ProvinceID = 9 Approved to supply component: ComponentID: 14 Vendor Code: N2U9870-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?

5. Insert • You can assign unique numbers, such as ID’s, to columns in your database using a sequence. Create Sequence VendorID increment by 1 start with 1; We will assume that the next available vendorid is 102 • We need to populate the data for the parent table (Vendor) first. Insert into Vendor (VendorID, VendorName, Address1, City, ProvinceID, Phone, Fax, Email, VendorFirstName, VendorLastName) Values (102, 'New2You', '9983 Buy Now Ave','Melbourne',9, '321-987-1001', '321-987-1000','tknew@new2you.com', 'Tracy', 'Knew'); • 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;

6. Insert • Next, we need to insert the data into the child (VendorComponent) table. Insert into VendorComponent (VendorID, ComponentID, VendorPartNumber, VendorPrice) Values ((Select max(VendorID) from Vendor), 14, ‘N2U9870-C’,45.98); We have to figure out what VendorID was assigned in the parent table. • Again, we will need to commit to make the changes permanent in the database. Commit;

7. Insert – Select CREATE TABLE FrenchVendor ( VendorID INTEGER, VendorName VARCHAR2(30), ProvinceID INTEGER, Address1 VARCHAR2(30), City VARCHAR2(20), PostalCode VARCHAR2(15) ); INSERT INTO FrenchVendor (VendorID, VendorName, ProvinceID, Address1, City, PostalCode) SELECT VendorID, VendorName, Province.ProvinceID, Address1, City, PostalCode FROM Vendor, Province, Country, Region WHERE Vendor.ProvinceID = Province.ProvinceID and Region.CountryID = Country.CountryID and Province.RegionID = Region.RegionID and CountryName = 'France';

8. Update Update the conversion rate of Canada to .00911. UPDATE Country SET CurrencyRate = .00911, CurrencyDate = sysdate WHERE CountryID = 2; 1 row updated. Updates the column values within one or more rows of a table.

9. Update Multiple Rows Increase the unit price of all Butane Homegens by 2%. UPDATE Product SET ProductPrice = ProductPrice * 1.02 WHERE ProductDescription Like '%Butane%' 21 rows updated.

10. Delete Delete ProductCode HG5000-01B from the PurchaseOrder table. DELETE FROM Product WHERE ProductCode = ‘HG5000-01B’; ERROR at line 1: ORA-02292: integrity constraint (Oracle.SYS_C0042860) 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).

11. Delete Multiple Rows Delete ProductCode HG5000-01B from the Product table. Step 1: Delete the child rows from the Manifest Table. DELETE FROM Manifest WHERE ProductID = (SELECT ProductID FROM Product WHERE ProductCode = 'HG5000-01B'); Step 2: Delete parent record from the PurchaseOrder Table. DELETE FROM Product WHERE ProductCode = 'HG5000-01B'; 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.

12. Workshop

13. Insert Add a new Survey record:

14. Update Change the values below for survey is 803.

15. Delete Delete survey 803.