Adding and Modifying Data

Introduction

So far, we have seen the following:

• CREATE TABLE: create a table.
• DROP TABLE: delete a table.

The table we have created so far did not contain any data.

We will now look at how we can: * Add data to a table. * Modify data. * Delete data.

First, we will create the trainees table again:

CREATE TABLE trainees (
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  email_address VARCHAR (255) UNIQUE NOT NULL
);

* This table currently contains no rows:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address

Columns: 3
Rows: 0

---

1. Adding a Row

With INSERT INTO and VALUES, you add a row to a table.

You do the following:

• INSERT INTO
  • Table name.
    • Column names in parentheses.
• VALUES
  • Values in parentheses.

In the example below, we add a row to the trainees table:

INSERT INTO
  trainees (
    first_name,
    last_name,
    email_address
  )
VALUES
  (
    'Adriana',
    'Goswin',
    'a.goswin@mail.com'
  );

Output:
1 rows affected

• We check the result:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address
Adriana	Goswin	a.goswin@mail.com

Columns: 3
Rows: 1

• If you try to add the same row again, you will get an error:

Output:
duplicate key value violates unique constraint "trainees_email_address_key"

• This is because the email_address column has the UNIQUE constraint.

---

2. Adding Multiple Rows

We have now added 1 row.

With INSERT INTO and VALUES, we can also add multiple rows.

In the example below, we add multiple rows to the trainees table:

INSERT INTO
  trainees (
    first_name,
    last_name,
    email_address
  )
VALUES
  (
    'Mirthe',
    'Duuk',
    'm.duuk@mail.com'
  ),
  (
    'Thijmen',
    'Jansen',
    't.jansen@mail.com'
  ),
  (
    'Paulien',
    'Spronk',
    'p.spronk@mail.com'
  );

Output:
3 rows affected

• We check the result:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address
Adriana	Goswin	a.goswin@mail.com
Mirthe	Duuk	m.duuk@mail.com
Thijmen	Jansen	t.jansen@mail.com
Paulien	Spronk	p.spronk@mail.com

Columns: 3
Rows: 4

---

3. Deleting Rows

We now have a table with 3 columns and 4 rows.

It may be that a participant has unsubscribed and needs to be removed.

With DELETE FROM and WHERE, we can delete rows.

• DELETE FROM allows you to delete a selection.
• With WHERE, you filter a selection to be deleted.

In the example below, we delete a selection from the trainees table:

DELETE FROM
  trainees
WHERE
  email_address = 'p.spronk@mail.com';

Output:
1 rows affected

• All rows filtered with the WHERE statement are deleted.
• 1 row is affected.
• We check the result:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address
Adriana	Goswin	a.goswin@mail.com
Mirthe	Duuk	m.duuk@mail.com
Thijmen	Jansen	t.jansen@mail.com

Columns: 3
Rows: 3

• If you want to delete all rows from a table, use TRUNCATE TABLE <table_name>;
  • Always think carefully before doing this.

---

4. Modifying Values

We now have a table with 3 columns and 3 rows.

It may be that, for example, an email address was entered incorrectly.

In that case, we need to modify it.

With UPDATE, SET, and WHERE, we can make modifications.

• UPDATE allows you to make a modification.
• From SET, you specify the intended modification.
• With WHERE, you filter a selection to be modified.

---

In the example below, we modify an email address:

• 't.jansen@mail.com' -> 't.jansen@mail.nl'.

UPDATE
  trainees
SET
  email_address = 't.jansen@mail.nl'
WHERE
  email_address = 't.jansen@mail.com';

Output:
1 rows affected

• All rows filtered with the WHERE statement are modified.
• 1 row is affected.
• We check the result:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address
Adriana	Goswin	a.goswin@mail.com
Mirthe	Duuk	m.duuk@mail.com
Thijmen	Jansen	t.jansen@mail.nl

Columns: 3
Rows: 3

• Here, you can specify what you want to modify using comparison (=, >, etc.) and logical operators (AND, OR, etc.).

---

5. Adding a Column

We now have a table with 3 columns and 3 rows.

It may be that additional details need to be added.

In that case, we need to add a column.

With ALTER TABLE and ADD, we can add columns.

• ALTER TABLE allows you to make a modification to the table structure.
• From ADD, you specify the new column(s).

---

In the example below, we add a new column:

• Named date_of_birth.
• With datatype DATE.

ALTER TABLE
  trainees
ADD
  date_of_birth DATE;

Output:
Query execution started
Query execution finished

• We check the result:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address	date_of_birth
Adriana	Goswin	a.goswin@mail.com	(NULL)
Mirthe	Duuk	m.duuk@mail.com	(NULL)
Thijmen	Jansen	t.jansen@mail.nl	(NULL)

Columns: 4
Rows: 3

• The new column currently contains no values.
• You can add these with UPDATE, SET, and WHERE.

---

6. Deleting a Column

We now have a table with 4 columns and 3 rows.

It may be that we made a mistake and the new column date_of_birth needs to be deleted.

With ALTER TABLE and DROP COLUMN, we can delete columns.

• ALTER TABLE allows you to make a modification to the table structure.
• From DROP COLUMN, you specify the column to be deleted.

---

In the example below, we delete a column:

• The column named date_of_birth.

ALTER TABLE
  trainees DROP COLUMN date_of_birth;

Output:
Query execution started
Query execution finished

• We check the result:

SELECT
  *
FROM
  trainees;

first_name	last_name	email_address
Adriana	Goswin	a.goswin@mail.com
Mirthe	Duuk	m.duuk@mail.com
Thijmen	Jansen	t.jansen@mail.nl

Columns: 3
Rows: 3

• The column is deleted.

---

Summary

• Adding rows:
  • With INSERT INTO and VALUES, you add rows to a table.
• Deleting rows:
  • With DELETE FROM and WHERE, you delete a filtered selection of rows.
  • With TRUNCATE TABLE, you delete all rows.
• Modifying values:
  • With UPDATE, SET, and WHERE, you make modifications to a filtered selection of rows.
• Adding a column:
  • With ALTER TABLE and ADD, you add a column.
• Deleting a column:
  • With ALTER TABLE and DROP COLUMN, you delete a column.