Home > Articles > Data > SQL

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account