Adding, Replacing, and Deleting Records

The following discussion excludes those parameters that specific relational databases support but which are not generally supported.
The positional versions of these commands that can be coded in programs are also excluded from this discussion.

Adding a Record

The purpose of the Insert statement is SQL (pronounced SEQUEL) is to add information to the database. You can insert either a single row or a selection of rows based on information stored elsewhere in the database. You need to have insert access to the table you are inserting into in order for the insert to work.

INSERT INTO [creator.]table-name
[(column-name, ... )]
VALUES (expression)
- or -
INSERT INTO [creator.]table-name
[(column-name, ... )]

If the optional list of column names are specified then the values are inserted into the corresponding columns. Any columns not specified must allow nulls. If no columns are specified then the values will be loaded into the columns in the order that the columns were specified when the table was created.

Specifying a select statement instead of a list of values will use the values in each row returned by the select as the values for a row to be inserted.

Rows are inserted in an arbitrary position within the table.

INSERT INTO stock VALUES ('5231', 'gds', 5)
INSERT INTO female_names (name, initials)
SELECT surname, initials
FROM names
WHERE sex='f'

go to top


Replacing a Record

The purpose of the Update statement is SQL is to replace information in the database. You need to have update access to the tables or views in order for the update to work.

UPDATE table-list SET
column-name=expression, ...
[WHERE search-condition]

The table-list is a list of the tables containing the columns that are to be updated. Each of the columns listed will be updated to contain the specified value.

The WHERE clause specifies the conditions that a table row must meet in order to be updated. If no where clause is specified then all rows in the table will be updated. It is also used to do joins between tables by specifying which fields in one table match to their corresponding foreign key fields in the other table.

Some relational databases allow views to be updated provided that the view does not contain a group by clause, an aggregate function, or involve a union. Other relational databases require all updates to be applied to the tables upon which the views are based.

UPDATE stock SET on_hand = 15 WHERE stock_code = '43263'

go to top


Deleting a Record

The purpose of the Delete statement is SQL is to remove information from the database. You need to have delete access to the table in order for the delete to work.

DELETE FROM [creator.]table-name
[WHERE search-condition]

A delete removes all rows from the table that satisfy the search condition. If no where clause is specified then all rows in the specified table will be deleted.

DELETE FROM student WHERE studnum = 42376


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow