PostgreSQL: How To Update & Delete Data
Tags: beginners, tutorial, postgres
Published: 2019-09-10 10:15:25

---
title: PostgreSQL: How To Update & Delete Data
published: true
description: PostgreSQL: How To Update & Delete Data
tags: beginners, tutorial, postgres
series: Intro to PostgreSQL (for NodeJS & Express)
---

## Intro

So we learned [how to create simple queries](https://dev.to/miku86/postgresql-how-to-read-from-write-to-our-table-48kg) to read & write some data.

We also learned [how to filter & order our data](https://dev.to/miku86/postgresql-how-to-create-some-basic-queries-57l8).

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

---

## Connect to the Database Shell

First, I connect to my created database shell:

```sql
psql -d miku86-db
```

---

## Read: Show All Data In `person`-table

Then, I check my current table:

```sql
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:

```sql
DELETE FROM [table] WHERE [expression];
```

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

```sql
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.

```sql
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):


```sql
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.

```sql
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:

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

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

```sql
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.

```sql
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](https://www.postgresql.org/docs/current/index.html)
[SQL Syntax](https://en.wikipedia.org/wiki/SQL_syntax)
[Delete Docs](https://www.postgresql.org/docs/current/sql-delete.html)
[Update Docs](https://www.postgresql.org/docs/current/sql-update.html)