PostgreSQL DELETE Row matching 1/2 Condition, Exits Example

Details

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

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

With One condition

The DELETE statement can be used with a single condition. The condition is set using the WHERE clause. Consider the Price table with the following data:

Price

Let us delete the record with an id of 4: 

DELETE FROM Price
WHERE id = 4;

The above command will delete the records in which the id is 4. Let us confirm whether the deletion was successful:

The row with an id of 4 has been deleted.

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)

  1. From the navigation bar on the left- Click Databases.
  2. 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)

  1. From the navigation bar on the left- Click Databases.
  2. 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)

  1. From the navigation bar on the left- Click Databases.
  2. 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:

Download the Database used in this Tutorial

 

YOU MIGHT LIKE: