This chapter deals with manipulating the data in tables with the statements:
•INSERT for inserting new rows into tables
•UPDATE for updating rows
•DELETE for deleting rows from tables
•CALL for manipulating data by executing procedures.
Access Privileges
You must have the appropriate access privileges on the relevant table(s) in order to use INSERT, UPDATE or DELETE.
In addition, the table itself must be updatable. All base tables are updatable, but some views are not, see Updatable Views.
In order to make a CALL you must have EXECUTE privilege on the procedure.
The INSERT statement is used to insert new rows into existing tables.
Values to be inserted may be specified explicitly, as constants or expressions, or in the form of a subquery, see below.
The data to be inserted must be of a type compatible with the corresponding column definition.
If the length of the inserted data differs from that of the column definition, the data is handled as follows:
|
Data |
Explanation |
|---|---|
|
Character strings |
If the inserted data is longer than the column definition, an error is reported and the INSERT operation fails (trailing spaces are truncated without error). If the inserted data is shorter than the column definition, it is padded to the right with spaces to the required length when inserted into a fixed-length character column. The inserted data is not padded when inserted into a VARCHAR or NCHAR VARYING column. |
|
Decimal values |
Decimal values which are longer than the column definition are truncated (not rounded) from the right to meet the column definition. Thus 12.3456 is inserted into DECIMAL(6,3) as 12.345. Decimal values which are shorter than the column definition are padded to the right of the decimal point with zeros. Thus 12.3 is inserted into DECIMAL(6,3) as 12.300. |
|
Integer values |
If the inserted data has more digits than the column definition or is outside the range of the definition, an error is reported and the INSERT operation fails. |
|
Floating point values |
Floating point values are converted to decimal by truncating the fractional part of the value as required by the scale of the decimal target. An error occurs if the scale of the target cannot accommodate the integral part of the value. |
|
Datetime values |
Date values are converted to timestamp by setting the hour, minute and second fields to zero. Time values are converted to timestamp by taking values for the year, month and day fields from CURRENT_DATE. Timestamp values are converted to date or time by discarding the field values that do not appear in the target. |
|
Interval values |
Single field interval values are converted to exact numeric by truncating decimal digits or by padding decimal digits with zeros. If any loss of leading precision occurs, or if overflow occurs, an error is raised. |
|
Binary values |
If the inserted data is longer than the column definition, an error is reported and the INSERT operation fails. If the inserted data is shorter than the column definition, and the column is fixed-length binary, an error is reported and the INSERT operation fails. |
The explicit INSERT statement has the general form:
INSERT INTO table [(column-list)]
VALUES (value-list);
Values in the value-list are inserted into columns in the column-list in the order specified.
The order of columns in the column-list need not be the same as the order in the table definition. Any columns in the table definition which are not included in the column-list are assigned null values, or the column default value if one is defined.
An explicit INSERT statement can only insert a single row.
For example:
Insert the values 'GW', 'Guinea-Bissau' and 'XOF' into the CODE, COUNTRY and CURRENCY_CODE columns respectively into the COUNTRIES table:
INSERT INTO countries(code, country, currency_code)
VALUES ('GW', 'Guinea-Bissau', 'XOF');
inserts the row:
|
CODE |
COUNTRY |
CURRENCY_CODE |
|---|---|---|
|
GW |
Guinea-Bissau |
XOF |
If you insert explicit values into all of the columns in a table, the column list can be omitted from the INSERT statement. The values specified are then inserted into the table in the order that the columns are defined in the table.
Thus the example above could also be written:
INSERT INTO countries
VALUES ('GW', 'Guinea-Bissau', 'XOF');
Inserting Results of Expressions
You can also insert the result of an expression into a table:
INSERT INTO mimer_store.customers(customer_id,
title, surname, forename,
date_of_birth,
address_1, address_2, town,
postcode, country_code,
email, password,
registered)
VALUES (DEFAULT,
'Mr', 'Eriksson', 'Sven',
mimer_store.cast_to_date('25/10/1953'),
'Kungsgaten 64', 'Box 1713', 'Uppsala',
'751 47', 'SE',
'training@mimer.com', 'secret',
CURRENT_DATE);
Values to be inserted can also be specified in the form of a subquery, i.e. fetched from a table in the database.
INSERT INTO formats
SELECT 11, 'Book & Cassette', MAX(formats.category_id),
MAX(display_order) + 10
FROM formats JOIN categories
ON formats.category_id = categories.category_id
WHERE category = 'Books';
Inserting the result of a subquery can insert a number of rows into a table. If any of the rows are rejected (e.g. because of a duplicate primary or unique key), the whole INSERT statement fails and no rows are inserted.
Inserting Sequence Values
The value to be inserted can be the value of a sequence. The constructs that return the current value or next value of a sequence can be used as column values in the INSERT statement:
INSERT INTO products(product, product_id)
VALUES ('SQL Reference', NEXT VALUE FOR product_id_seq);
INSERT INTO mimer_store_music.titles(item_id, artist_id)
VALUES (CURRENT VALUE FOR mimer_store.item_id_seq, 500999);
The keyword NULL may be used to insert the null value into a column, provided that the column is not defined as NOT NULL:
INSERT INTO tracks(item_id, track_no, track, length)
VALUES (60099, 14, 'Bayamesa', NULL);
The null indicator is implicitly inserted into columns when no value is given for that column and the column definition does not include a default value.
Thus, the following INSERT statement will give the same results as the example above:
INSERT INTO tracks(item_id, track_no, track)
VALUES (60099, 14, 'Bayamesa');
Data in existing table rows can be changed with the UPDATE statement. This statement has the general form:
UPDATE table
SET column = value
WHERE search-condition;
The search condition specifies which rows in the table are to be updated. If no search condition is specified, all rows will be updated.
Update the exchange rate for US dollars to 0.8886:
UPDATE CURRENCIES
SET EXCHANGE_RATE = 0.8886
WHERE CODE = 'USD';
Discount all Sony prices by 10 percent:
UPDATE items
SET price = price * 0.90
WHERE producer_id IN (SELECT producer_id
FROM producers
WHERE producer = 'Sony');
Primary key columns can be updated provided the table is stored in a databank with TRANSACTION or LOG option.
The DELETE statement removes rows from a table, and has the general form:
DELETE FROM table
WHERE search-condition;
The search condition specifies which rows in the table are to be deleted. If no search condition is specified, all rows will be deleted (the table is emptied but not dropped).
Delete all countries that begin with the letter 'D' from the COUNTRIES table:
DELETE FROM countries
WHERE country LIKE 'D%';
Delete all rows from the CUSTOMERS table:
DELETE FROM customers;
Delete all Sony items:
DELETE FROM mimer_store.items
WHERE producer_id IN (SELECT producer_id
FROM mimer_store.producers
WHERE producer = 'Sony');
In addition to using data manipulation statements directly, as just described, it is also possible to manipulate table data by calling a procedure. Procedures perform the specific data manipulations laid out in the procedure definition.
Any SQL statement in the grouping procedural-sql-statement, see the Mimer SQL Reference Manual, Procedural SQL Statements, can be used in a procedure, and this includes all the data manipulation statements.
The use of procedures allows data manipulation within the database to be controlled both in terms of strictly defining which data manipulation operations are performed and also in terms of regulating which database objects can be affected.
In the CALL statement, the value-expressions or assignment targets specified for each of the procedure parameters must be of a data type that is assignment-compatible, see the Mimer SQL Reference Manual, Assignments, with the parameter data type.
See the Mimer SQL Reference Manual, CALL, for full details of the CALL statement and the Mimer SQL Programmer's Manual, Mimer SQL Stored Procedures, for a general discussion of the stored procedure functionality supported in Mimer SQL.
Examples of Calling Procedures
Invoke the procedure called SEARCH in the MIMER_STORE_MUSIC schema:
CALL mimer_store_music.search(:title, :artist, CAST(NULL as integer));
INSERT, UPDATE and DELETE statements may be used on views.
The operation is then performed on the base table upon which the view is defined. However, certain views may not be updated (for example a view containing DISTINCT values, where a single row in the view may represent several rows in the base table).
A view is not updatable if any of the following conditions are true:
•the keyword DISTINCT is used in the view definition
•the select list contains components other than column specifications, or contains more than one specification of the same column
•the FROM clause specifies more than one table reference or refers to a non-updatable view
•the GROUP BY clause is used in the view definition
•the HAVING clause is used in the view definition
Note:By defining an INSTEAD OF trigger any view can be made updatable. If all the INSTEAD OF triggers on the view are dropped, the view will revert to not updatable if one or more of the above conditions are true.