8.3.4 Inserting, Updating and Deleting Data

8.3.4 Inserting, Updating and Deleting Data

A database must change as real-world information changes. SQL data-maintenance statements can add a new tuple, alter values in existing tuples or remove tuples that are no longer required. These operations must be written carefully because they change stored data rather than merely displaying a result.

The EcoTrack Tables

The examples continue the original EcoTrack database. A research site is the parent table, and each sensor stores a foreign key that identifies its site.

Current ResearchSite data

Current Sensor data

Adding a Tuple with INSERT INTO

A clear insertion names the target table, lists the attributes that will receive values, and then supplies those values in the same order.

INSERT INTO Sensor
    (SensorID, SiteID, SensorType, InstalledOn,
     DailyCheckTime, SampleInterval)
VALUES
    ('S-826', 'RS318', 'Wind speed', '2026-05-18',
     '08:30', 12);

Some SQL systems permit an insertion without an attribute list when every value is provided in the table's defined order. Naming the attributes is clearer and avoids relying on remembered column order.

Matching values to attributes

How the DBMS Checks an Insertion

An INSERT INTO statement is not accepted automatically. The DBMS checks the proposed tuple against the table definition and its constraints.

Insert a parent before its child

Suppose a sensor will be installed at a completely new site. The parent ResearchSite tuple must exist before the child Sensor tuple can reference it.

INSERT INTO ResearchSite
    (SiteID, SiteName, RegionCode, Active)
VALUES
    ('RS530', 'Coastal Dune', 'NE', TRUE);

INSERT INTO Sensor
    (SensorID, SiteID, SensorType, InstalledOn,
     DailyCheckTime, SampleInterval)
VALUES
    ('S-913', 'RS530', 'Sand movement', '2026-06-02',
     '09:00', 20);

Changing Existing Values with UPDATE

UPDATE identifies the table, SET states the new value, and WHERE selects the tuple or tuples to be changed.

UPDATE Sensor
SET SampleInterval = 8
WHERE SensorID = 'S-405';

Updating several matching tuples deliberately

A condition may intentionally match more than one tuple:

UPDATE Sensor
SET SampleInterval = 20
WHERE SensorType = 'Air quality';

This changes all three air-quality sensor tuples. The statement is valid only when that wider change is the intended result.

Updating more than one attribute

UPDATE ResearchSite
SET RegionCode = 'W',
    Active = TRUE
WHERE SiteID = 'RS424';

Removing Tuples with DELETE FROM

DELETE FROM removes complete tuples. The WHERE clause identifies which tuples are to be removed.

DELETE FROM Sensor
WHERE SensorID = 'S-218';

This deletes one sensor tuple because SensorID is the table's primary key.

Deleting several matching tuples

DELETE FROM Sensor
WHERE SiteID = 'RS205';

This removes every sensor whose foreign key is 'RS205'. In the current data, two tuples match.

DELETE FROM Sensor
WHERE SensorID = 'S-218';

DELETE FROM Sensor;

Referential Integrity and Operation Order

A parent tuple cannot usually be deleted while child tuples still reference its primary key. The dependent sensor tuples must be dealt with first.

DELETE FROM Sensor
WHERE SiteID = 'RS205';

DELETE FROM ResearchSite
WHERE SiteID = 'RS205';

A Safer Maintenance Workflow

SQL maintenance statements can affect permanent stored data. A disciplined process reduces accidental changes.

Previewing an update condition

SELECT SensorID, SensorType, SampleInterval
FROM Sensor
WHERE SensorType = 'Air quality';

If the preview returns three tuples, the corresponding update condition will affect those same three tuples.

Worked Example: Retiring a Site and Adding Its Replacement

Step 1: Remove child tuples

DELETE FROM Sensor
WHERE SiteID = 'RS205';

Step 2: Remove the parent tuple

DELETE FROM ResearchSite
WHERE SiteID = 'RS205';

Step 3: Insert the new parent tuple

INSERT INTO ResearchSite
    (SiteID, SiteName, RegionCode, Active)
VALUES
    ('RS640', 'River North', 'N', TRUE);

Step 4: Insert the new child tuple

INSERT INTO Sensor
    (SensorID, SiteID, SensorType, InstalledOn,
     DailyCheckTime, SampleInterval)
VALUES
    ('S-950', 'RS640', 'Water flow', '2026-08-12',
     '07:45', 15);

Interactive: Data Maintenance Simulator

Test insertions, trace update conditions and explore how referential integrity affects deletion order.

-- Choose an operation

Common Mistakes and Misconceptions

Practice

Review