Informix Modifying Data

Deleting Rows

The DELETE statement removes any row or combination of rows from a table. You cannot recover a deleted row after the transaction is committed.

Deleting All Rows of a Table

The following query will delete all rows from the customer table.

DELETE FROM customer

Deleting a Known Number of Rows

DELETE FROM customer WHERE customer_num = 175

Deleting an Unknown Number of Rows

DELETE FROM customer WHERE company = 'Druid Cyclery'

Complicated Delete Conditions

Suppose you discover that some rows of the stock table contain incorrect manufacturer codes. Rather than update them, you want to delete them so that they can be reentered. You know that these rows, unlike the correct ones, have no matching rows in the manufact table. The fact that these incorrect rows have no matching rows in the manufact table allows you to write a DELETE statement such as the one in the following example:

DELETE FROM stock
WHERE 0 = (SELECT COUNT(*) FROM manufact
        WHERE manufact.manu_code = stock.manu_code)

Inserting Rows

Single Rows

INSERT INTO stock
 
    VALUES(115, 'PRC', 'tire pump', 108, 'box', '6/box')

Multiple Rows and Expressions

This SELECT statement returns two columns. The data from these columns (in each selected row) is inserted into the named columns of the cust_calls table. Then, an order number (from order_num, a serial column) is inserted into the call description, which is a character column.

INSERT INTO cust_calls (customer_num, call_descr)
 
    SELECT customer_num, order_num FROM orders
 
        WHERE paid_date IS NOT NULL
 
        AND ship_date IS NULL

Updating Rows

Selecting Rows to Update

The first form of an UPDATE statement uses a series of assignment clauses to specify new column values, as the following example shows:

UPDATE customer
 
    SET fname = 'Barnaby', lname = 'Dorfler'
 
    WHERE customer_num = 103

You can also use subqueries in the WHERE clause.

UPDATE orders
 
    SET backlog = 'y'
 
    WHERE ship_date IS NULL
 
    AND order_num IN
 
        (SELECT DISTINCT items.order_num FROM items
 
            WHERE items.stock_num = 6
 
            AND items.manu_code = 'ANZ')

Updating with Uniform Values

Suppose the manufacturer code HRO has raised all prices by 5 percent, and you must update the stock table to reflect this increase. Use a statement such as the following :

UPDATE stock
 
    SET unit_price = unit_price * 1.05
 
    WHERE manu_code = 'HRO'

Database Privileges

http://www.dbcenter.cise.ufl.edu/triggerman/infoshelf/sqlt/04.fm2.html

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.