USING clause

In this section you will learn how to implement the USING clause of PostgreSQL through the KSearch library.

USING clause

PostgreSQL doesn’t support the DELETE JOIN statement. However, it does support the USING clause in the DELETE statement that provides similar functionality as the DELETE JOIN.

The syntax of this type of method is given as follows:

• using(String table)

where:

• table: Name of the new table to be added in the USING clause.

Examples

Example 1: Delete all products from "Jumbo" store

Java code:

final String storeName = "Jumbo";        K.table("product p").using("store s").whereEqualColumn("p.store_id", "s.id").where("s.name", storeName).delete();

SQL generated:

DELETE FROM product pUSING store sWHERE p.store_id = s.idAND s.name = ?1

Parameters:

• ?1 → "Jumbo"

Example 2: Delete all stores in the "Colombia" country

Java code:

final String countryName = "Colombia";        K.table("store s").using("city ci").using("country co").whereEqualColumn("s.city_id", "ci.id").whereEqualColumn("ci.country_id", "co.id").where("co.name", countryName).delete();

SQL generated:

DELETE FROM store sUSING city ci, country coWHERE s.city_id = ci.idAND ci.country_id = co.idAND co.name = ?1

Parameters:

• ?1 → "Colombia"