SQL statement exercise-update and delete data

========================================================== ==================================

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)