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.
Top comments (0)