CODEWITHSUNDEEP
postgresqlsubquerysql-delete
davideghz
davideghz

Reputation: 3685

SQL DELETE FROM table records matching subquery

I have a subquery that returns

 one_id   | two_id
--------------------
 1654838  | 1526634
 1657136  | 1526634
 1659179  | 1526634
 1659527  | 1526634
 2040608  | 1510944
 2040608  | 1516727

I have a table_x like

 one_id   | two_id  | other_column
-----------------------------------
 1654838  | 1526634 | ...
 ...      | ...     | ...

How can I DELETE records FROM table_x having one_id and two_id listed in subquery result?

If the subquery would return only one_id I could use something like

DELETE FROM table_x WHERE `one_id` IN (SELECT ...

Is there a similar solution with the given subquery result?

Upvotes: 1

Views: 974

Answers (2)

Luky
Luky

Reputation: 5406

I don't know what's the impact on performance but you can try something like:

DELETE FROM table_x WHERE CONCAT(one_id, two_id) IN (SELECT CONCAT(one_id, two_id) ...

Probably also casting and/or aliasing is needed.

Upvotes: 0

Max Senft
Max Senft

Reputation: 630

Update

Yes, the below answer was not correct. I had the chance to test it. But this should work:

DELETE FROM
    mytable2 AS t1
WHERE EXISTS (
    SELECT
        one_id, two_id
    FROM
        mytable1 t2
    WHERE
        t1.one_id=t2.one_id AND
        t1.two_id=t2.two_id
)

Update 2: Add an additional WHERE AND clause which will give you the wanted one_id and two_id values. Example:

    WHERE
        (t2.someothercol > 0 AND t2.someothercol < 10) AND
        t1.one_id=t2.one_id AND
        t1.two_id=t2.two_id

Original answer

Maybe something like this (I couldn't test it though):

WITH my_subquery AS (
    SELECT one_id, two_id
    FROM some_table
)
DELETE FROM table_X AS t1
WHERE t1.one_id=my_subquery.one_id AND t1.two_id=my_subquery.two_id

Reference to PostgreSQL documentation: https://www.postgresql.org/docs/9.1/static/queries-with.html

Upvotes: 2

Related Questions