September, 2019
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.
First, I connect to my created database shell:
psql -d miku86-db
person
-tableThen, 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.
Simplified Syntax:
DELETE FROM [table] WHERE [expression];
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.
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.
Simplified Syntax:
UPDATE [table] SET [col] = [newvalue] WHERE [expression];
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
.
We will learn how to connect two tables.
Hi! I'm Michael π I'm a Mentor & Educator & Senior Web Developer - I help you to reach your (career) goals.