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