SQL Basics - Changing and Deleting Data

Last post we discussed adding a new entry into a table. Today I want to talk about updating previous data, or deleting it. It's actually quite simple. To update, we use the UPDATE clause. Cool, isn't it? Our customer, Fun4all moved, and we want to change his address.

UPDATE Customers
SET cust_address = '123 Drunk Drive'
WHERE cust_id='100000003'

This replaces the customer's old address, 1 Sunny Place, with his new address.

WARNING!!! WARNING!!! WARNING!!! WARNING!!!

If I had left out the WHERE clause, this query would have gone through without a problem, except that now all my customer's addresses are '123 Drunk Drive'. You must make sure to use the WHERE clause.

If we need to change his city and zip too, we can use the same query qith the new fields separated by commas.

UPDATE Customers
SET cust_address = '123 Drunk Drive',
cust_city = 'Jailcell',
cust_zip = '07734'
WHERE cust_id='100000003'

To delete data is even easier, using the DELETE clause. Fun4all moved and isn't buying from us ever again, that loser. Let's take him out of our database.

DELETE FROM customers
WHERE cust_id = '100000003'

Here again we must be careful to use the WHERE clause, or we just erased our whole customer base. Not a good move.

Next time we'll discuss changing the table itself, and creating new tables.