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.

---

Further Reading

PostgreSQL Docs
SQL Syntax
Delete Docs
Update Docs