Updating Data with SQL

In this module, we discuss how to add and delete data from a relational database.  Because we not just accessing the data but are making changes to them, we also study how to backup and restore the database.  In the module on "Creating and Changing Databases with SQL," we will learn how to create and alter the structure of a database.  Because we should employ well-established principles in these tasks, we discuss how to design databases properly before covering the latter module.

Insertions

Once created, we can put data into a table.  For example, to insert the tuple (SubjectA, +, M) into table CurrentBioStudy, we write the following insert statement:

insert into CurrentBioStudy(subject, RhFactor, BloodType)

values ('SubjectA', '+', 'M');

 Alternatively, using a set clause, we can place the values with the column names in any order, as follows:

insert into CurrentBioStudy

set RhFactor = '+', BloodType = 'M', subject = 'SubjectA';

 

 In MySQL, if we have a value for every column, we can omit the column names:

insert into CurrentBioStudy

values ('SubjectB', '-', 'M');

For an apostrophe to be part of the constant, we employ \' to avoid terminating the string.  For example, "Smith's lab" in the values clause is written as 'Smith\'s lab'.  To store a backslash, we must write two backslashes, as in 'C:\\main' to represent "C:\main".

If we do not specify a value, the database stores null in that field unless the field has a default value.  Also, we can use the constant null as a value, such as follows:

insert into CurrentBioStudy(subject, RhFactor, BloodType)

values ('SubjectC', null, 'M');

          MySQL allows us to insert several tuples at once, as the following illustrates:

insert into CurrentBioStudy

values ('SubjectD', null, 'M'),

       ('SubjectE', '+', 'MN'),

       ('SubjectF', '-', 'N');

 

insert into LabSection (CatalogNumber, SecYear,   

   semester, section, DragExpWeek)

values ('Phys201', 2004, 'F', 'A', 3);

We can read data from a file and insert the data into a table with a load statement.  We must create the table so that the columns agree with the data.  If tabs separate the data items and a newline character terminates each tuple in the file, we can insert the data of file lab.dat, which is in the subdirectory /home/johnson/db/, into table LabSection, as follows:  

load data infile '/home/johnson/db/lab.dat'

into table LabSection;

To load lab.dat from the current local directory, we can issue the following command using local:

load data infile local lab.dat

into table LabSection;

 

Updating

Some databases change frequently.  To modify the row of a table, we use the update statement.  For example, to update the SubjectA tuple of the table CurrentBioStudy so that the Rh-factor is negative and the blood type is MN, we write the following:

update CurrentBioStudy

set    RhFactor = '-', BloodType  ='MN'

where  subject = 'SubjectA';  

Although optional, the where clause occurs frequently in update statements.

Example 1.         Suppose table ChemExperiment of a chemical database has the following organization:

Suppose in Experiment 37, we dissolve a 13.5-gram sample of a nonelectrolyte in 100 grams of benzene, and the solution freezes at 2.95�C.  To insert this information into the table, we type the following:

insert into ChemExperiment

values (37, 13.5, �benzene�, 100, 2.95);

If later we want to change every occurrence of �benzene� in the table to start with a capital letter, we issue an update command, as follows:

update ChemExperiment

set   SolventName = �Benzene�

where SolventName = �benzene�;

If the update is successful, MySQL issues a message that resembles the two lines below.  One row was affected, matched, and changed.

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1 Warnings: 0

If the syntax of the update statement was correct, but the system found no rows that matched the where clause, a message like the following would appear:

Query OK, 0 row affected (0.01 sec)

Rows matched: 0  Changed: 0 Warnings: 0

If we know the row for which we are searching is in the database, we probably mistyped a data item, such as using Phys203 instead of Phys201. 

If a statement has an SQL syntax error, MySQL issues an error message and gives the approximate location of the mistake.

To update the quantity of benzene to be 100 grams in every row in which the amount is greater than 100, we use the relational operator greater than (>) and the logical operator && (or and), as follows:

update ChemExperiment

set    SolventMass = 100

where  SolventName = �benzene� &&

       SolventMass > 100;

 

update LabSection

set DragExpWeek = 2

where

   CatalogNumber  = �Phys201� and

   SecYear  =  2004  and

   semester =  �F�   and

   section  = �A�;

Row Deletions

We also use the where clause in a delete statement to delete a row or rows from a table.  To delete the row for Experiment 37, we give the table and the particular key, as follows:

delete from ChemExperiment

where ExpNumber = 37;

 If we wish to delete every row that has a solute mass less than 1 gram, we write

delete from ChemExperiment

where SoluteMass < 1;

We must be very careful of such massive changes that could change a number of rows.  It is advisable to make a backup of the database before issuing such commands.  Later in this module, we discuss how to make backups.  An even more dangerous delete statement does not have a where clause, resulting in removal of every row from the table.

 # CAUTION: Removes every row from the table

delete from ChemExperiment;

 

Backups

 Making backups is extremely important, particularly considering the amount of data that most databases store.  The mysqldump utility, which we access from the operating system prompt, not in MySQL, stores ANSI SQL commands to recreate a database or a table.  For example, we can use mysqldump to store SQL statements to create the tables and insert the data from the PsyDB database. If the database is password protected, we must also designate the user (here root) after �u and the password (here dbpass) immediately after -p.  The following utility call at the operating system prompt creates a file, PsyDBBkup.sql, of SQL statements:

 mysqldump -u root -pdbpass PsyDB > PsyDBBkup.sql

 PsyDBBkup.sql, which begins as follows, contains SQL statements to recreate every table and its data: 

# MySQL dump 5.13

#

# Host: localhost    Database: PsyDB

#--------------------------------------------------------

# Server version    3.22.22

 

#

# Table structure for table 'PsyEvent'

#

CREATE TABLE PsyEvent (

  EventCode int(11) DEFAULT '0' NOT NULL,

  event char(30),

  PRIMARY KEY (EventCode)

);

 

#

# Dumping data for table 'PsyEvent'

#

 

INSERT INTO PsyEvent VALUES (1,'\'Left press\'');

INSERT INTO PsyEvent VALUES (2,'\'Right press\'');

INSERT INTO PsyEvent VALUES (3,'\'Turn on all panel lights\'');

�

To backup the table PsyExp from that database, we give the database and table names after mysqldump, as follows:

 mysqldump  -u root -pdbpass  PsyDB PsyExp > PsyExp.sql

To recreate the database or table, we can execute the commands in the file at the operating system prompt. If PsyDB exists, but the PsyExp no longer does, we can recreate the table and its data using redirection in UNIX, as follows:

 mysql  -u root -pdbpass PsyDB  < PsyExp.sql

 Because the backup files are in ASCII format, we can also search for data with which to perform a partial backup.

Exercises

1.      Consider a database called anthropology with a discovery relation having the following schema:

(ItemID: int, DiscoveryDate: date, longitude: float, latitud float,condition: { 'EXCELLENT', 'VERYGOOD', 'GOOD', 'POOR', 'VERYPOOR'},carbon14:float)

        Write statements to do each of the following tasks:

a.      Add three rows of data all at one time to the table discovery

b.      Update the first tuple to have a condition of POOR

c.      Remove the last row added to the table