Transcript Insert & Dele.. - Integrated Statistics

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:
VendorName: New2You
Address: 9983 Buy Now Ave
Miami FL 32940
Contact: Tracy Knew
Phone: 321-987-1001
Fax:
321-987-1000
Email: [email protected]
FL  ProvinceID = 9
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?
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 VendorID 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 (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','[email protected]', '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 (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.
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 FrenchVendor (
VendorID
VendorName
ProvinceID
Address1
City
PostalCode
);
INTEGER,
VARCHAR2(30),
INTEGER,
VARCHAR2(30),
VARCHAR2(20),
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';
Page 7
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
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.
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 ProductPrice = ProductPrice * 1.02
WHERE ProductDescription Like '%Butane%'
21 rows updated.
Page 9
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
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).
Page 10
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
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.
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
Q1
5
Q2
6
Q3
7
Q4
3
Q5
4
Q6
8
Q7
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
Q1
4
Q2
Q3
6
Q4
Q5
8
Q6
Q7
2
Page 14
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.
Delete
Delete survey 803.
Column
Value
Surveyid
803
Customerid
Userid
Requiredproductid
surveydate
Q1
4
Q2
Q3
6
Q4
Q5
8
Q6
Q7
2
Page 15
•Confidential & Proprietary
Copyright © 2009 Cardinal Directions, Inc.