INSERT, UPDATE, DELETE

--inserts updates deletes
use MagazineSubscription

Select * from Magazine

insert into Magazine(MagName,MagType)
Values('Cat fancy','Monthly')

--insert multiple rows in a single statement
--only in 2008 and later
Insert into Magazine(MagName,MagType)
Values('Poprocks and Coke', 'weekly'),
('Fast Company','Quarterly')

Create table Magazine2
(
MagID int,
MagName varchar(100),
MagType char(10)
)

--referential integrity
--inserts you cannot insert into a child table unless
--there is an existing related value in a parent table
--you cannot insert a sale that doesn't have an existing
--customer in a customer table
--it forces you to enter the customer before the sale

--with updates
--you cannot change the foreign key value
--except to an existing value in the parent table

--Delete you cannot delete a parent that has children
--unless you first delete the children

Insert into Magazine2(MagID, MagName, MagType)
(Select MagID, MagName, MagType from Magazine)

Select * from Magazine2

Select * from Customer

Update Customer
Set CustFirstName='Martha',
CustAddress='1000 North Mercer Street'
Where CustID=1

--You can use a transaction to manually start and end
--the transaction. It gives you an undo option with
--rollback
Begin transaction

--this is fine with the where clause
--without the where clause it sets every
--record in the table to 'weekly'
UpDate Magazine2
Set MagType ='weekly'
Where MagID=2

Select * from MagazineDetail

--this changes all the prices
--raising them by 10%
Update MagazineDetail
Set SubscriptionPrice = SubscriptionPrice * 1.1

--either rollback or commit
--rollbakc undoes every thing
Rollback tran--

Commit Tran --writes everythin

--works because 13 has no child records
Delete from Magazine
Where MagID=13

--will delete all subscriptions because
--they are all child records
Delete from subscription

Select * from Subscription