This chapter is from the book
This chapter is from the book
This chapter is from the book
Updating and Deleting Data
Data cannot only be inserted and selected from a table. UPDATE and DELETE are two essential commands for modifying and removing data from a table. The syntax of UPDATE and DELETE is similar to that of SELECT statements. In this section, you will learn to use those two commands efficiently.
Writing UPDATE Queries
SQL supports UPDATE queries. Updating means that the database searches for records that fulfill certain conditions and changes the values of some fields of those records. The syntax of UPDATE queries is simple:
Command: UPDATE Description: Replaces values of columns in a table Syntax: UPDATE table SET col = expression [, ...] [ FROM fromlist ] [ WHERE condition ]
First you define the table. After the SET token, you specify a list of columns that have to be updated. With the help of the FROM clause, a table can be updated by taking data from other tables into consideration. The WHERE clause can be used like the WHERE clause of SELECT statements; the syntax is the same.
Let's look at some examples. We will write a query that updates the sales table by changing the amount of books bought by John from 2 to 3:
UPDATE sales SET amount=3 WHERE id=3;
You see, the query is indeed very simple. Use the following to determine whether the query has been performed correctly:
SELECT id, amount FROM sales WHERE id=3;
If no error has been displayed, the result is
id | amount ----+-------- 3 | 3 (1 row)
The update has been completed successfully. Now we want to lower the price of all books in the database by 1$. This example also shows that an UPDATE statement does not always need a WHERE clause:
UPDATE products SET price=price-1;
The query assigns the price of the product minus 1 dollar to the products. UPDATE queries can also use subqueries to calculate the result of a value that has to be updated. Here is an example of assigning the price from table sales to the price in table products:
UPDATE products
SET price=
(SELECT MAX(prodprice)
FROM sales
WHERE prodid='385343')
WHERE prodid='385343';
The update has been done for exactly one record, which is correct. The next example uses UPDATE queries with multiple columns. We will update the prices in table products with the value from table sales:
UPDATE products
SET price=sales.prodprice
FROM sales
WHERE products.prodid=sales.prodid;
Update queries can also contain self-joins. The next example changes all entries from Linux to UNIX for all books that are assigned to the categories Handbook and Linux:
UPDATE prodcat
SET prodcat='UNIX'
FROM prodcat AS a
WHERE prodcat.prodid=a.prodid
AND prodcat.prodcat='Linux'
AND a.prodcat='Handbook';
The database performs an update for product number 394568 and now has the following entries:
shop=# SELECT * FROM prodcat WHERE prodid='394568'; id | prodid | prodcat ----+--------+---------- 6 | 394568 | Handbook 5 | 394568 | UNIX (2 rows)
Using DELETE
The DELETE command is used to remove records that match certain criteria from a table. Here is an overview of the DELETE command's syntax:
Command: DELETE Description: Removes rows from a table Syntax: DELETE FROM table [ WHERE condition ]
We create a table called temp and insert some values into it:
SELECT prodid, price, stored INTO temp FROM products;
The table now contains the following:
prodid | price | stored --------+-------+-------- 385342 | 48.99 | 64.00 765354 | 28.99 | 98.00 106666 | 39.99 | 120.00 385343 | 44.99 | 20.00 394568 | 39.99 | 18.00 (5 rows)
The next query deletes all books that are more expensive than 40 dollars:
DELETE FROM temp WHERE price > 40;
The database deletes two records. If you want to delete all records from the table, you write the following:
DELETE FROM temp;
The table is now empty. The DELETE command is simple. The only mistake people make is to write DELETE * FROM table instead of DELETE FROM table. This mistake leads to a syntax error, and the DELETE can't do its job because DELETE removes a record completely. It is not possible to remove half of a dataset; you would have to update a column to NULL instead of deleting it.