6. Manipulating Data

In this chapter we study the Data Manipulation Language (DML) part of SQL which is used to make changes to the data inside a relational database. The three basic commands of DML are as follows.

We have already seen a few examples on the INSERT statement including simple inserts and selective field insertions. Thus we will concentrate on other ways to use this statement.

6.1 Inserting NULL’s

In previous chapters, we have seen that not specifying a column value while doing selective field insert operations results in a null value being set for them. We can also explicitly use the keyword NULL in SQL to signify null values in statements like INSERT.

1 INSERT INTO proglang_tbl VALUES (4, 'Tcl', 'Ousterhout', '1988', NULL);

Running a query to show the contents of the entire table helps us to verify the result.

1 SELECT * FROM proglang_tbl;

6.2 Inserting data into a table from another table

You can insert new records into a table from another one by using a combination of INSERT and SELECT. Since a query would return you some records, combining it with an insertion command would enter these records into the new table. You can even use a WHERE conditional to limit or filter the records you wish to enter into the new table. We will now create a new table called stdlang_tbl, which will have only two fields - language and standard. In this we would insert rows from the proglang_tbl table which have a non-null value in the standard field. This will also demonstrate our first use of a boolean operator - NOT.

1 CREATE TABLE stdlang_tbl (language varchar(20), standard varchar (10));
2 
3 INSERT INTO stdlang_tbl SELECT language, standard FROM proglang_tbl WHERE standa\
4 rd IS NOT NULL;

When you view the contents of this table, you will notice that it has picked up the two languages which actually had a standard column value.

6.3 Updating existing data

To modify some data in a record, we use the UPDATE command. While it cannot add or delete records (those responsibilities are delegated to other commands), if a record exists it can modify its data even affecting multiple fields in one go and applying conditions. The general syntax of an UPDATE statement is given below.

1 UPDATE <table_name> SET
2  <column1> = <value>,
3  <column2> = <value>,
4  <column3> = <value>
5  . . .
6 WHERE <condition>;

Let us now return to our proglang_tbl table and add a new row about the Forth programming language.

1 INSERT INTO proglang_tbl VALUES (5, 'Forth', 'Moore', 1973, NULL);

We later realize that the language actually was created near 1972 (instead of 1973) and it actually has been standardized in 1994 by the ANSI. Thus we write our update query to reflect the same.

1 UPDATE proglang_tbl SET year = 1972,
2   standard = 'ANSI' WHERE language = 'Forth';

If you’ve typed the statement correctly and no errors are thrown back, the contents of the record in question would have been modified as intended. Verifying the result of the same involves a simple query the likes of which we have seen in previous examples.

6.4 Deleting data from tables

You can use the DELETE command to delete records from a table. This means that you can choose which records you want to delete based on a condition, or delete all records but you cannot delete certain fields of a record using this statement. The general syntax of the DELETE statement is given below.

1 DELETE FROM <table_name> WHERE <condition>;

While putting a conditional clause in the DELETE is optional, it is almost always used. Simply because not using it would cause all the records to be deleted from a table, which is a rarely valid need. We now write the full statement to delete the record corresponding to Forth from the table.

1 DELETE FROM proglang_tbl WHERE language = 'Forth';