SQL Data Manipulation

Structured Query Language (SQL) allows database manipulation using INSERT, UPDATE, and DELETE commands. Data Manipulation Language (DML) allows modification of data in relational databases using:

  • INSERT (add new records)
  • UPDATE (modify existing records)
  • DELETE (remove records)

 

1. Inserting data

The INSERT statement is used to add new records to a table. The basic syntax is:

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

 

Using our students table as an example:

INSERT INTO students (id, first_name, last_name, date_of_birth, email, city, enrolled)
VALUES (1, 'John', 'Doe', '2000-05-15', 'john.doe@example.com', 'Amsterdam', '2025-09-01');

This will add a new student with the specified values.

 

Bulk insert

Bulk inserts are useful for importing data efficiently. When using a bulk insert, we setup the fields and add multiple records seperated by a comma ,.

INSERT INTO students (first_name, last_name, date_of_birth, email, city, enrolled)
VALUES 
    ('Alice', 'Smith', '2001-04-12', 'alice.smith@example.com', 'Amsterdam', '2025-09-01'),
    ('Bob', 'Brown', '2002-06-24', 'bob.brown@example.com', 'Utrecht', '2025-09-01'),
    ('Charlie', 'Taylor', '2000-12-19', 'charlie.taylor@example.com', 'Rotterdam', '2025-09-01');

This will insert 3 records at once and increases processing speed.

 

From another table

Sometimes you want to add a lot of data that is partially in another table, you can query that data while inserting rows dynamically based on conditions.

INSERT INTO enrollments (student_id, course_id, academic_year)
SELECT id, 3, 2025 FROM students WHERE city = 'Rotterdam';

This will insert students that are origination from city = 'Rotterdam' in the enrollments table for course_id 3 and academic_year 2025.

 

CSV insert

PostgreSQL supports Unix-like composability, meaning it can easily work with external files and commands. Just as Unix tools can pass output from one process as input to another, PostgreSQL allows you to import or export data using standard file formats like CSV. This makes data exchange simple and scriptable.

Example: Importing data from a CSV file

COPY students (first_name, last_name, date_of_birth, email, city, enrolled)
FROM '.\export\students.csv'
DELIMITER ','
CSV HEADER;

Benefits of COPY:

  • Fast bulk operations – Optimized for large datasets.
  • Easy integration – Works seamlessly with files generated by other systems.
  • Automation-friendly – Ideal for ETL processes and scheduled imports/exports.

Drawbacks of COPY:

  • Limited data transformation – You can’t easily normalize or clean data during import.
  • Error handling is basic – A single bad row can cause the entire import to fail.
  • Less flexible – Harder to apply business logic compared to using a script.

Tip: For complex imports (e.g., data validation, normalization, or enrichment), consider using a scripting language like Python with libraries such as pandas or psycopg2. This gives you full control over data processing before inserting into the database.

 

Try it yourself :rocket:

  • Try to INSERT yourself as a student into the database.
  • How about your teacher? Check if he/she is in the database and if not add them.

  

2. Updating data

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE students
SET city = 'Amsterdam'
WHERE email = 'john.doe@example.com';

This will update the city of all students matching john.doe@example.com to 'Amsterdam'.

[!WARNING] Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, ALL records in the table will be updated!

 

Updating multiple rows based on conditions

In the above example we showed how to update based on a condition, an email is in most condition an unique field for a student, but if we use a condition that is much wider in range this will also work.

UPDATE students
SET city = 'The Hague'
WHERE city = 'Rotterdam';

This will update the city of all students to 'The Hague' currently living in 'Rotterdam'.

 

Updating multiple columns at once

Up until now we just update a single column in our database table, but in most cases we want to update multiple columns of a record that matches our condition.

It is the WHERE clause that determines how many records will be updated.

UPDATE courses
SET department = 'Data Science', credits = 6
WHERE name = 'Database Systems';

This will update the city of all students to 'The Hague' currently living in 'Rotterdam'.

 

Updating using data from another table 

UPDATE students
SET enrolled = '2025-09-01'
WHERE id IN (SELECT student_id FROM enrollments WHERE academic_year = 2025);

 

[!CAUTION] Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

 

Try it yourself :rocket:

  • Search all students that live in 'Losser', how many students are there?
  • Update these students to now live in 'Lutjebroek'
  • Update your own enrollment year in the database to be '01-09-2025'

  

3. Deleting data

The DELETE statement is used to delete existing records in a table.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM students
WHERE email = 'john.doe@example.com';

This will delete all students with email 'john.doe@example.com'.  

[!WARNING] Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, ALL records in the table will be deleted!

 

Deleting multiple rows based on a condition 

DELETE FROM enrollments
WHERE academic_year < 2020;

 

Deleting data using subqueries 

DELETE FROM enrollments
WHERE student_id IN (SELECT id FROM students WHERE city = 'Amsterdam');

 

[!CAUTION] Be careful when deleting records. If you omit the WHERE clause, ALL records will be deleted!

 

Try it yourself :rocket:

  • Try deleting all students that live in 'Lutjebroek', make sure that all records belonging with this student inside other tables are also deleted.

  

Best practices

  • Use transactions when modifying multiple related records (next workshop).
  • Always test updates and deletions on sample data before applying to live data.
  • Use bulk operations for efficient insertions and updates.
  • Regularly backup your database before making structural changes.

 

This site uses Just the Docs, a documentation theme for Jekyll.