Page 1 of 3
© Claire Ellul
Database Manipulation Language
The database manipulation language (DML) is used to insert, update and delete
data from the database once the database and associated objects have been
created using the Database Definition Language (DDL).
DML statements can be run through a command-line interface, creating one
item of data at a time, or data can be bulk loaded through data loading tools
such as Oracle’s SQL Loader. DML statements modify the database content,
and in PostgreSQL changes are automatically saved by default.
Three key statements exist within this language:
Insert
This command allows new rows to be added to a table. For example, to add a
new record to an EMPLOYEES table having the following structure:
NI_Number
Name
Surname
Department
Grade
Salary
Use the following statement:
INSERT INTO ucfscde.EMPLOYEES
(NI_Number, Name, Surname, Department, Grade, Salary)
VALUES
(‘B 29296875’,’David’,’Gower’,’IT’,’Consultant’,15000);
Note the use of quotation marks around the string values. Also the number of
attributes in the first list should match the number of values in the second list.
Page 2 of 3
© Claire Ellul
Update
Once a row has been added, it can be changed in two ways:
1. Delete the row and reinsert it from scratch
2. Use the update command to update some elements of the data
The first option obviously causes problems particularly if the row forms part of
a primary key referenced in a child table. Attempting to delete the row may
result in a referential integrity error – the database will not know whether to
keep or delete the child record.
Updating the row is therefore safer in most cases, but it should be noted that
some values may not be updatable – these include automatically generated
primary keys or date and time stamps.
To give David Gower a salary increase of £10,000 the following statement can
be issued:
UPDATE ucfscde.EMPLOYEES
SET SALARY = 25000
WHERE SURNAME = ‘Gower’;
Note the use of the WHERE clause to ensure that only David Gower got the
increased salary.
Page 3 of 3
© Claire Ellul
Delete
This is the equivalent of the DROP statement in DML and should be used with
great care. It allows deletion of one or more rows from a table. Again, where
a row is a parent reference to another row in the child table, the row will not
be able to be deleted as a referential integrity error will be generated.
Particular care should be taken with the statement:
DELETE *
FROM ucfscde.EMPLOYEES;
As this will delete all records in the employees table.
Once again, the WHERE statement is used to identify the records to delete:
DELETE
FROM ucfscde.EMPLOYEES
WHERE SURNAME = ‘Gower’;