Monday, November 15, 2010

Modifying data: UPDATE and DELETE

In the last post we covered getting data into tables using INSERT and COPY. In this post we focus on modifying data in table using UPDATE and DELETE.

UPDATE as one might imagine updates or modifies values in a table, including NULLs. The basic idea is that one is specifying or SETting the new values of one or more fields FROM a certain table WHERE the rows meet some condition.

SuperFoods supermarket has a table for their employees:

create database "SuperFoods";
\c "SuperFoods";
create table employees (lastname varchar, firstname varchar, title varchar);
insert into employees values('Doe','Jane', 'cashier');
insert into employees values('Frost','Mary', 'manager');
insert into employees values('Smith','John', 'shift leader');

SuperFoods=# select * from employees;
 lastname | firstname |    title    
 Doe      | Jane      | cashier
 Frost    | Mary      | manager
 Smith    | John      | shift leader
(3 rows)

John Smith has worked hard and has been promoted from shift leader to assistant manager. HR needs to update the employees table and set the title to be 'assistant manager' where the row corresponds to John Smith. We do that with:

SuperFoods=# update employees SET title='assistant manager' WHERE lastname='Smith' AND firstname='John';
SuperFoods=# select * from employees;
 lastname | firstname |       title      
 Doe      | Jane      | cashier
 Frost    | Mary      | manager
 Smith    | John      | assistant manager
(3 rows)

We don't have to update one row at a time. We can update many rows simultaneously. Suppose SuperFoods is having a promotion and are going to reduce the price of all their wine from a certain winery by 10%.

create table wines (winery varchar, wine varchar, price decimal);
insert into wines values('Vinny''s Vino','Smooth Syrah',9.99);
insert into wines values('Wacky Wines','Zany Zin',8.99);
insert into wines values('Wacky Wines','Magic Merlot',19.99);
insert into wines values('Oregon Oeno','Perky Pinot',14.50);

SuperFoods=# select * from wines;
    winery    |     wine     | price
 Vinny's Vino | Smooth Syrah |  9.99
 Wacky Wines  | Zany Zin     |  8.99
 Wacky Wines  | Magic Merlot | 19.99
 Oregon Oeno  | Perky Pinot  | 14.50
(4 rows)

Notice for "Vinny's Vino" we needed to double up on the single quote inside the string. Without that PostgreSQL will get confused, think that string is 'Vinny' and then complain that the rest of statement starting with 's Vino' is not valid.

OK, let's reduce the price on all Wacky Wines:

update wines set price=0.9*price where winery='Wacky Wines';

SuperFoods=# update wines set price=0.9*price where winery='Wacky Wines';
SuperFoods=# select * from wines;
    winery    |     wine     | price 
 Vinny's Vino | Smooth Syrah |   9.99
 Oregon Oeno  | Perky Pinot  |  14.50
 Wacky Wines  | Zany Zin     |  8.091
 Wacky Wines  | Magic Merlot | 17.991
(4 rows)

(Note: decimal is not the best type for a price but is fine for illustrative purposes here. Notice too that the updated rows are now at the end of the table.)

We are setting price field for any rows where winery='Wacky Wines' to be 0.9 times the original price and this leads to 2 rows being updated.

If we wanted to update the price of all wines, it would have been very simple indeed: update wines set price=0.9*price; as there would have been no need for a WHERE clause.

Usefully, we can update multiple columns in a single query by putting the SET components in a comma separated list:

update tablename set col1='newvalue1', col2='newvalue2' WHERE some_condition;

With UPDATE we are updating individual fields of some or all of the rows of a tables. With DELETE, however, we are deleting complete rows; there is no need to specify any columns. (Deleting complete columns is not achieved with delete. For that we use ALTER TABLE...DROP COLUMN covered later.) Thus, we just need to specify the table of interest and the focal set of rows:

delete from table_name where some_condition;

Despite SuperFoods' wine promotion, they are not bringing in enough customers. Times are tough and they have to let one of their cashiers go. To delete Jane Doe from the employees table we can do:

SuperFoods=# delete from employees where lastname='Doe' and firstname='Jane';

If we don't specify a WHERE condition, say delete from wines, what will happen? This will mean that all rows are of interest and the table will be emptied. The table will remain, it will not be deleted from the database completely (for that we would need the DROP TABLE command), but it will not contain any rows until you do an INSERT or COPY. If you do want to empty a table it is actually better to use a special command, TRUNCATE TABLE which is more efficient.

You can also specify that you want to see the set of rows that were deleted. To do this, tack on a "returning *" to the end of the query:

SuperFoods=# delete from wines where price=9.99 returning *;
    winery    |     wine     | price
 Vinny's Vino | Smooth Syrah |  9.99
(1 row)


Few databases are completely static. Data change and they need to be cleaned, modified and purged. UPDATE and DELETE are both simple yet powerful. There isn't however a simple undo command if you make a mistake so use with caution. If in doubt, make a copy of the table (create table new_table as select * from old_table), test out your queries and then run them against the intended table.

1 comment: