PostgreSQL: How To Update & Delete Data

Intro

So we learned how to create simple queries to read & write some data.

We also learned how to filter & order our data.

Now we want to learn how to update & delete data.

Connect to the Database Shell

First, I connect to my created database shell:

psql -d miku86-db

Read: Show All Data In `person`-table

Then, I check my current table:

miku86-db=# SELECT * FROM person;
 id | nickname | country | available
----+----------+---------+-----------
  1 | miku86   | germany | t
  2 | heidi    | austria | t
  3 | tom      | usa     | f
  4 | pavel    | russia  | f
(4 rows)

I SELECT all (* = wildcard) rows FROM the person-table. There are 4 rows in it.

Delete A Row

Simplified Syntax:

DELETE FROM [table] WHERE [expression];

Example #1: Delete the person(s) with the `id` of `1`:

miku86-db=# DELETE FROM person WHERE id = 1;
DELETE 1

You can see DELETE 1 below the DELETE-query. The command executed successfully, 1 row was deleted.

miku86-db=# SELECT * FROM person;
 id | nickname | country | available
----+----------+---------+-----------
  2 | heidi    | austria | t
  3 | tom      | usa     | f
  4 | pavel    | russia  | f
(3 rows)

The person with the id of 1 was deleted.

Example #2: Delete the person(s) that are not available (=> available = false):

miku86-db=# DELETE FROM person WHERE available = false;
DELETE 2

You can see DELETE 2 below the DELETE-query. The command executed successfully, 2 rows were deleted.

miku86-db=# SELECT * FROM person;
 id | nickname | country | available
----+----------+---------+-----------
  2 | heidi    | austria | t
(1 row)

The person with the id of 1 was deleted in example 1. The persons with available of f (= false) were deleted in this example.

Update A Row

Simplified Syntax:

UPDATE [table] SET [col] = [newvalue] WHERE [expression];

Example #1: Update the person(s) with `country` of `austria` to `country` of `germany`:

miku86-db=# UPDATE person SET country = 'germany' WHERE country = 'austria';
UPDATE 1

You can see UPDATE 1 below the UPDATE-query. The command executed successfully, 1 row was updated.

miku86-db=# SELECT * FROM person;
 id | nickname | country | available
----+----------+---------+-----------
  2 | heidi    | germany | t
(1 row)

The person with the country of austria was updated to the country of germany.

Next Part

We will learn how to connect two tables.