A Brief Intro
The USING clause on DELETE statement is basically let us join multiple table
and delete only the data from those join.
If you ever use mysql DELETE JOIN with INNER JOIN, then postgresql USING
is kind of similar to that except it can only delete from one table by
default.
How?
How to do that? Here’s an example:
Let’s say we have table A and table B which is referenced to table C by id. If
we use SELECT statement on table C with INNER JOIN on table A, it would look
like this:
SELECT *
FROM C
INNER JOIN A
ON A.C_id = C.id;
Now, if we want to only delete all records from table C related to table A
without deleting all records from table C related to table B, we can use
DELETE with USING clause like this:
DELETE
FROM C
USING A
WHERE A.C_id = C.id;
Please keep in mind that if you haven’t set the
ON DELETE CASCADEfor table A on referenceC_id, that query will throw an error.
Alright, that’s it. See you next time!