Data Manipulation Language in SQL (DML)
SQL language also includes syntax to update, insert, and delete records. These query and update commands together form the Data Manipulation Language (DML) part of SQL:
• INSERT INTO – inserts new data into a database table
• UPDATE – updates data in a database table
• DELETE – deletes data from a database table
• SELECT – extracts data from a database table
How to Insert Data
Having now built the structure of the database it is time to populate the tables with some data. In the vast majority of desktop database applications data entry is performed via a user interface built around some kind of GUI form. The form gives a representation of the information required for the application, rather than providing a simple mapping onto the tables. So, in this sample application you would imagine a form with text boxes for the user details, drop-down lists to select from the PC table, drop-down selection of the software packages etc. In such a situation the database user is shielded both from the underlying structure of the database and from the SQL which may be used to enter data into it. However we are going to use the SQL directly to populate the tables so that we can move on to the next stage of learning SQL.
The command to add new records to a table (usually referred to as an append query), is:
INSERT INTO target [(field1[, field2[, …]])]
VALUES (value1[, value2[, …]);
So, to add a User record for user Jim Jones, we would issue the following INSERT query:
INSERT INTO User (FirstName, LastName, UserID, Dept, EmpNo, PCType) 6
VALUES (“Jim”, “Jones”, “Jjones”,”Finance”, 9, “DellDimR450”);
Obviously populating a database by issuing such a series of SQL commands is both tedious and prone to error, which is another reason why database applications have front- ends. Even without a specifically designed front-end, many database systems – including MS Access – allow data entry direct into tables via a spreadsheet-like interface.
The INSERT command can also be used to copy data from one table into another. For example, The SQL query to perform this is:
INSERT INTO User ( FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet )
SELECT FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet FROM NewUsers;
How to Update Data
The INSERT command is used to add records to a table, but what if you need to make an amendment to a particular record? In this case the SQL command to perform updates is the UPDATE command, with syntax:
UPDATE table
SET newvalue
WHERE criteria;
For example, let’s assume that we want to move user Jim Jones from the Finance department to Marketing. Our SQL statement would then be:
UPDATE User
SET Dept=”Marketing”
WHERE EmpNo=9;
Notice that we used the EmpNo field to set the criteria because we know it is unique. If we’d used another field, for example LastName, we might have accidentally updated the records for any other user with the same surname.
The UPDATE command can be used for more than just changing a single field or record at a time. The SET keyword can be used to set new values for a number of different fields, so we could have moved Jim Jones from Finance to marketing and changed the PCType as well in the same statement (SET Dept=”Marketing”, PCType=”PrettyPC”). Or if all of the Finance department were suddenly granted Internet access then we could have issued the following SQL query:
UPDATE User
SET Internet=TRUE
WHERE Dept=”Finance”;
You can also use the SET keyword to perform arithmetical or logical operations on the values. For example if you have a table of salaries and you want to give everybody a 10% increase you can issue the following command:
UPDATE PayRoll
SET Salary=Salary * 1.1;
How to Delete Data
Now that we know how to add new records and to update existing records it only remains to learn how to delete records before we move on to look at how we search through and collate data. As you would expect SQL provides a simple command to delete complete records. The syntax of the command is:
DELETE [table.*]
FROM table
WHERE criteria;
Let’s assume we have a user record for John Doe, (with an employee number of 99), which we want to remove from our User we could issue the following query:
DELETE *
FROM User
WHERE EmpNo=99;
In practice delete operations are not handled by manually keying in SQL queries, but are likely to be generated from a front end system which will handle warnings and add safe-guards against accidental deletion of records.
Note that the DELETE query will delete an entire record or group of records. If you want to delete a single field or group of fields without destroying that record then use an UPDATE query and set the fields to Null to over-write the data that needs deleting. It is also worth noting that the DELETE query does not do anything to the structure of the table itself, it deletes data only. To delete a table, or part of a table, then you have to use the DROP clause of an ALTER TABLE query.
