PostgreSQL DELETE Row matching 1/2 Condition, Exits Example

In PostgreSQL, the DELETE statement is used to delete either one or more records from a table. If you want to delete select rows from a table PostgreSQL allows you to combine the DELETE statement with the WHERE clause else it will delete all records.

In this PostgreSQL Tutorial, you will learn the following:

Syntax

The DELETE statement takes the syntax given below:

[ WITH [ RECURSIVE ] with-query [, ...] ]
DELETE FROM [ ONLY ] table-name [ * ] [ [ AS ] alias ]
    [ USING using-list ]
    [ WHERE condition(s) | WHERE CURRENT OF cursor-name]
    [ RETURNING * | output-expression [ [ AS ] output-name] [, ...] ]

Parameters

with-query: the WITH clause allows us to reference one or more subqueries to be referenced by name in DELETE query.
table-name: the name of the table from which records are to be deleted.
alias: this is a substitute for the name of the target table.
using-list: table expressions to allow columns from other tables to be used in WHERE clause.
condition(s): optional. They are the conditions that must be satisfied for records to be deleted. If this section is not provided, all table-name records will be deleted.
cursor-name: the cursor to be used in WHERE CURRENT OF condition. The lastly fetched row by this cursor will be deleted.
output-expression: the expression to be processed and returned by DELETE statement after the deletion of every row.
output-name: the name to be used for the returned column.

Note that since the DELETE statement deletes the entire row, you don’t need to specify the column names.

With Two conditions

The PostgreSQL DELETE statement can take two conditions. The two conditions should be joined using the AND operator. We will use the following table:

Price:

Consider the example given below:

DELETE FROM Price
WHERE id = 3Y
AND price = 300;

In the above command, we are deleting the row in which the id is 3, and price is 300. We can now query the table:

SELECT * FROM Price

This Returns the following:

The record with an id of 3 and a price of 300 was deleted.

Using EXISTS Condition

With the EXISTS condition, you can make the DELETE more complex. Sometimes, there may be a need to delete records in one table based on records in another table.

You will see that FROM clause does not allow you to list records from more than one table when performing delete, the EXISTS clause becomes very useful. We have the following two tables:

Book:

Price:

We can then run the following query:

DELETE FROM Book
WHERE EXISTS
  (SELECT 1
    FROM Price
    WHERE Price.id = Book.id
    AND price < 250 );

The above command will delete from the Book table where there exists a record in the Price table with an id matching that of the Book table and the price being less than 250.

The Book table is now as follows:

The record with an id of 1 was deleted.

Using pgAdmin

Now let’s see how these actions can be performed using pgAdmin.

With One condition

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

From the navigation bar on the left- Click Databases.
Click Demo.

Step 3) Type the query in the query editor:

DELETE FROM Price
WHERE id = 4;

Step 4) Click the Execute button.

Step 5) Let us check whether the deletion was successful:

With Two conditions

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

From the navigation bar on the left- Click Databases.
Click Demo.

Step 3) Type the query in the query editor:

DELETE FROM Price
WHERE id = 3
AND price = 300;

Step 4) Click the Execute button.

Step 5) Let us check whether the deletion was successful:

Using EXISTS Condition

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

From the navigation bar on the left- Click Databases.
Click Demo.

Step 3) Type the query in the query editor:

DELETE FROM Book
WHERE EXISTS
  (SELECT 1
    FROM Price
    WHERE Price.id = Book.id
    AND price < 250 );

Step 4) Click the Execute button.

Step 5) Let us check whether the deletion was successful:

Summary:

The DELETE statement is used for deleting one or more records from a table.
To delete only select rows from a table, you can combine the DELETE statement with the WHERE clause.
If the DELETE clause is used without the WHERE clause, it deletes all records from the table.
table-name parameter allows you to add the name of the table from which records are to be deleted.
We can use the DELETE statement with one condition, specified using the WHERE clause.
The DELETE statement can also be used with two conditions specified in the WHERE clause. The two conditions should be joined using the AND operator.
The EXISTS condition can help us delete records from a table based on the records of another table.

Download the Database used in this Tutorial

LOGIN

PostgreSQL DELETE Row matching 1/2 Condition, Exits Example