Structured Query Language: Updating Data in Databases

Databases are not static. Data are inserted, deleted and updated. New tables are created. Old tables are removed or modified. Previous lecture presented how to create, modify, and delete tables. It also presented how to insert new records into database. This lecture shows how to modify existing data, and how to remove selected data from database.

Second part of the presented lecture demonstrates that SQL can be used beyond Microsoft Access, and queries work also in other systems. For this purpose we will be using PostgreSQL database system.

Modifying data in databases

To change existing data in databases SQL used UPDATE command. The command has very simple syntax. Key word UPDATE is followed by tables to be updated, values to be set, and conditions specifying in which records the change should be made.

UPDATE <table names> SET <list of fields their and new values > WHERE <condition>;

For example, the command:

UPDATE patient

SET pat_sex = ‘Female’

WHERE pat_sex like ‘*F*’ ;

places value “Female” in all patient records in which the field previously contains letter “f”. Thus, all different spellings, such as “F”, “fem”, “Fem.”, “Female”, are unified to one form “Female.” Similarly, the following command updates all other, non empty, values to have value “Male.”

UPDATE patient

SET pat_sex = ‘Male’

WHERE pat_sex not like ‘*F*’ AND pat_sex is not null ;

The result of applying the two above queries is presented in Figure 1.

Figure 1: Patient table with changed values for patient sex.

To update value in one specific record, one needs to be able to uniquely identify that record. For example, when updating patient’s record, it is not correct to specify first and last names, because they may not be unique. Only the primary key uniquely identifies records in a table. Thus, in order to update date of birth of a specific patient one may use that patient’s record number to access data (assuming that the number is the primary key). This is exemplified by the following query. Patient table before and after running the query are presented in Figures 2 and 3, respectively.

update patient

SET DateOfBirth = '02/12/1967'

where recordnumber = 'A45' ;

Figure 2: Patient table before updating date of birth for patient A45.

Figure 3: Patient table after updating date of birth for patient A45.

Be careful! If you do not specify criteria for update, all records will be changed. For example, the following query assigns date of birth to all patients in database. This is usually not what you want.

UPDATE patient

SET DateOfBirth = '02/12/1967' ;

Figure 4: Patient table after running UPDATE query without specifying criteria.

Similarly to SELECT queries, it is possible to write UPDATE queries that use more than one table. One way to do it is similar to implicit joins when selecting data. The same method can be used to update values in one table based on values in another table. For example, suppose the task is to add $10 extra charge to all services related to diabetes, for all claims in which the charge is non zero. Recall from the previous lectures, that the following query can be used to display all claims related to diabetes. It’s result is illustrated in Figure 5.

SELECT *

FROM claim, icd9

WHERE claim.dignss_c = icd9.icd9 AND icd9.ICDDescription like '*diab*' AND claim.blld_amt > 0;

In order to update the billing about for all of the above claims, the following query can be used. Result of the update is illustrated in Figure 6. Note that the qupdate query with multile tables is not supported by all RDBMSs. A standard query supported by all SQL based systems is described below.

UPDATE claim, icd9

SET claim.blld_amt = claim.blld_amt + 10

WHERE claim.dignss_c = icd9.icd9 AND icd9.ICDDescription like '*diab*' AND claim.blld_amt > 0;

Figure 5: Claims related to diabetes.

Figure 6: Updated billed amount for claims related to diabetes.

The above form of updating multiple tables is very convenient, but unfortunately it is not standard in SQL. It works in Access, but not in many other systems. To solve this problem, one needs to use WHERE EXISTS condition. The syntax for this condition is following:

UPDATE <table> SET <field> = value WHERE EXISTS (<select query>);

The following query can be used to increase charge for patients with diabetes by $10.

UPDATE claim

SET blld_amt = blld_amt + 10

WHERE EXISTS

( SELECT * from icd9

WHERE claim.dignss_c = icd9.icd9 AND icd9.ICDDescription like '*diab*' AND claim.blld_amt > 0

);

When applied to database presented in Figure 6, the query produces result presented in Figure 7.

Figure 7: Claims with increased amount for diabetic patients.

Other SQL-based database systems

The majority of this course was based on Microsoft Access RDBMS which provides graphical interface and is easily available. There are, however, many more database systems, many of which based on SQL.

In this lecture we will use PostgreSQL RDBMS to illustrate that SQL can be used beyond Access. Although specific installation steps and some commands may differ from system to system the underlying language is the same.

Go to the lecture about PostgreSQL.

You can also see old lecture about MySQL.

Use SQL to Modify Data

In this assignment you are asked to create SQL queries. Please prepare your queries without using graphical interface in Access. Some questions may need to be done in multiple steps and require mare than one query.

Using claim, patient, and icd9 tables:

- Create field “diabetic” in the table patient. The field should be text with one character.

- Insert values into the field “diabetic”. Insert value “Y” for all patients that have claims related to diabetes, and “N” to all other patients.

- Encode patient’s gender in their record numbers. If patient’s record number is XXXX and the patient is Male, then new record number should be MXXXX. Similarly, if YYY is patient number for a female patient, the new number should be FYYY. Make corrections in all tables in which patient numbers are used.

Please submit SQL queries (text), and screenshots of design view of tables.