SQL Basics

• UPDATE can be used in two ways
  1. Update specific rows
  2. Update all rows
• For safety, always use a WHERE clause with UPDATE
• Without a WHERE clause, all rows are updated!

-- Oracle / MySQL / SQL Server	
UPDATE   Customers
SET      cust_contact = 'Hans Kazan',
         cust_email = '123@123.com'
WHERE    cust_id = '1000000006'; 

One row is updated

• UPDATE statements can also contain subqueries
• Set a field to NULL to remove its assigned value

• DELETE can be used in two ways
  1. Delete a specific row
  2. Delete all rows
• When deleting a row, use always a WHERE clause
• Without a WHERE clause, all rows are deleted!

-- Oracle / MySQL / SQL Server	
DELETE FROM Customers
WHERE cust_id IN ('1000000006','1000000007','DLL01');  

One row is deleted

• A foreign key relates one table to another
• Referential integrity rules can prevent deletion of a row referenced by another table
• DELETE does not specify individual columns: it removes rows, not columns
• DELETE removes rows, never the table itself

• A column can also be removed using a different statement

-- Oracle / MySQL / SQL Server
ALTER TABLE custCopy
DROP COLUMN cust_email;	

• TRUNCATE TABLE is an alternative to DELETE when removing every row from a table
• TRUNCATE can be faster because its logging differs from DELETE
• Whether TRUNCATE can be rolled back depends on the DBMS

-- Oracle / MySQL / SQL Server
TRUNCATE TABLE custCopy; 	

An UPDATE or DELETE without WHERE affects every row in the table. Handle these statements carefully. The following guidelines are commonly used:

1. Never use UPDATE or DELETE without WHERE when targeting specific rows
2. Give every table a primary key and use it in the WHERE clause
3. Before updating or deleting, test the WHERE clause in a SELECT statement
4. Use database-enforced referential integrity to protect relationships between tables
5. As a database administrator, restrict updates and deletes without WHERE

67. Change the vend_name of the vendor added in exercise 65 to 'Toys R Us'

68. Remove the row added in exercise 65 from Vendors