========================================================== ==================================
Title: SQLStatement exercise-Update and delete data
Note: SQL SERVER 2000
Date: 2011.4.17
Name: Zhu minglei
========================================================== ==================================
1Update a row and a column
UPDATE MERs
SET cust_email = 'Kim @ thetoystore.com'
WHERE cust_id = '123'
Verify whether the update is successful:
SELECT cust_id, cust_email
FROM MERs
WHERE cust_id = '123'
2Update a row with multiple columns
UPDATE MERs
SET cust_contact = 'Sam Roberts ',
Cust_email = 'Sam @ toyland.com'
WHERE cust_id = '123'
Verify whether the update is successful:
SELECT cust_id, cust_contact, cust_email
FROM MERs
WHERE cust_id = '123'
3Update all rows and columns
UPDATE MERs
SET cust_email = 'Kim @ thetoystore.com'
Verify whether the update is successful:
SELECT cust_email FROM MERs
Exercise caution when updating data. If the WHERE clause is missing, all rows in the table are updated.
4Delete a row
Delete from MERs
WHERE cust_id = '123'
Verify whether the update is successful:
5Deletes the value of a column.
UPDATE MERs
SET cust_email = NULL
6Delete all rows
Delete from MERs
Note: although all rows of the Customers table are deleted, the Customers table itself is not deleted.
7Delete all rows in the TABLE, with a faster TRUNCATE TABLEStatement.(SQlite does not support this statement)