INSERT, DELETE, and UPDATE Statements in SQL

 

INSERT, DELETE, and UPDATE Statements in SQL

In SQL, database modification is performed using three fundamental commands:

  1. INSERT – to add new data

  2. DELETE – to remove existing data

  3. UPDATE – to modify existing data

Unlike retrieval queries, these commands change the database state and therefore must obey all integrity constraints defined in the schema (domain, key, entity integrity, and referential integrity constraints).

6.4.1 The INSERT Command

The INSERT command is used to add new tuples (rows) into a table.

1. INSERT with Complete Tuple

In its simplest form, INSERT specifies:

  • The table name

  • A complete list of attribute values in the same order as defined in CREATE TABLE

INSERT INTO EMPLOYEE
VALUES ('Richard','K','Marini','653298653','1962-12-30',
        '98 Oak Forest, Katy, TX','M',37000,'653298653',4);

📌 Important rules:

  • Number of values must match the number of attributes

  • Data types must be compatible

  • Order must match the table schema

  • All integrity constraints are checked

2. INSERT with Selected Attributes

Often, not all attribute values are known. SQL allows insertion by specifying only some attributes.

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES ('Richard','Marini',4,'653298653');

📌 Behavior:

  • Attributes not listed:

    • Get their DEFAULT value, or

    • Become NULL (if allowed)

  • Attributes declared NOT NULL and without DEFAULT must be included

3. Constraint Enforcement during INSERT

A DBMS enforces all constraints automatically.

Example: Referential Integrity Violation

INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
VALUES ('Robert','Hatcher','980760540',2);

❌ Rejected if no department with Dnumber = 2 exists.

Example: Entity Integrity Violation

INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES ('Robert','Hatcher',5);

❌ Rejected because Ssn is the primary key and cannot be NULL.

4. Inserting Multiple Tuples

SQL allows inserting multiple rows in a single command:

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES
('Amit','Sharma',3,'111223333'),
('Neha','Verma',4,'222334444');

5. INSERT with Query Result (INSERT … SELECT)

INSERT can be combined with a SELECT query to populate a table.

Step 1: Create the Table

CREATE TABLE WORKS_ON_INFO
( Emp_name VARCHAR(15),
  Proj_name VARCHAR(15),
  Hours_per_week DECIMAL(3,1) );

Step 2: Insert Query Results

INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)
SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

📌 This creates a snapshot table.

  • Data may become outdated

  • To maintain consistency → views are preferred

6. Bulk Loading and Table Copying

Bulk Loading

  • DBMS tools load data from files efficiently

  • Faster than repeated INSERT commands

Creating and Loading a Table Using LIKE

CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
 FROM EMPLOYEE AS E
 WHERE E.Dno = 5)
WITH DATA;

📌 This:

  • Copies table structure

  • Loads selected data immediately

6.4.2 The DELETE Command

The DELETE command removes tuples from a table.

General Syntax

DELETE FROM table_name
WHERE condition;

  • Deletes rows, not columns

  • Affects only one table

  • May trigger cascading actions due to referential integrity

Examples

Delete specific employees

DELETE FROM EMPLOYEE
WHERE Lname = 'Brown';

➡ Deletes zero or more rows

Delete one specific tuple

DELETE FROM EMPLOYEE
WHERE Ssn = '123456789';

➡ Deletes exactly one tuple (primary key match)

Delete multiple tuples

DELETE FROM EMPLOYEE
WHERE Dno = 5;

➡ Deletes all employees in department 5

Delete all tuples

DELETE FROM EMPLOYEE;

⚠️ Table structure remains
To remove the table completely → DROP TABLE

Referential Integrity Effects

If ON DELETE CASCADE is defined:

  • Deleting a tuple may automatically delete related tuples in other tables

If ON DELETE SET NULL:

  • Referencing foreign keys are set to NULL

6.4.3 The UPDATE Command

The UPDATE command modifies attribute values in existing tuples.

General Syntax

UPDATE table_name
SET attribute = new_value
WHERE condition;

  • Modifies rows, not schema

  • WHERE clause selects affected tuples

  • SET clause defines new values

Example 1: Updating Specific Attributes

UPDATE PROJECT
SET Plocation = 'Bellaire', Dnum = 5
WHERE Pnumber = 10;

➡ Updates a single project

Example 2: Updating Multiple Tuples

UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;

📌 Important concept:

  • Right-hand Salary → old value

  • Left-hand Salary → new value

Setting NULL or DEFAULT Values

UPDATE EMPLOYEE
SET Super_ssn = NULL
WHERE Ssn = '999887777';

UPDATE EMPLOYEE
SET Dno = DEFAULT
WHERE Ssn = '888665555';

Referential Integrity Effects of UPDATE

If a primary key is updated:

  • Changes may cascade to foreign keys (ON UPDATE CASCADE)

  • Or be rejected (RESTRICT)

  • Or set to NULL / DEFAULT

Summary of SQL Modification Commands

CommandPurpose
INSERT        Adds new tuples
DELETE        Removes existing tuples
UPDATE        Modifies existing tuples

Key Observations

  • All commands obey integrity constraints

  • Operations may cascade across tables

  • Each command modifies one table at a time

  • Multiple changes require multiple commands

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more