postgreSQL: DELETE by matching row data

Question

I have the results of a SELECT statement that I would like to turn around and use to delete the matching rows from a different table with the same data. I don't have the specific details from the SELECT -- just the rows returned.

For example, if I have the tables

table_1
name  | value
--------------
Bob   | 5
Ben   | 14
Bev   | 8


table_2
name  | value
--------------
Bob   | 5
Bill  | 2
Biff  | 4

And I'm given the results of a SELECT from the first table that returns Bob's row, how can I use that to delete Bob from the second table?

Edit To clarify more what I'm doing, I have a script that is going through several pairs of backup/live tables and finds rows that are new and updated (that's the SELECT part I'm given -- its a change log). Now, I need to delete some of those new rows but all I have to work with is the raw data from the change log and the name of the table. For inserts this isn't a problem:

INSERT INTO table_name <row data here>  -- Don't need column names

I was hoping DELETE had a similar schema-free way of deleting stuff by matching raw data similar to how an EXCEPT clause works.

do you want the raw SQL query for this?

ROOT
– ROOT

2020-03-05 18:04:55 +00:00
Commented Mar 5, 2020 at 18:04 — ROOT
– ROOT, Commented Mar 5, 2020 at 18:04
Raw SQL would be ideal but I'm open to other solutions

user2859458
– user2859458

2020-03-05 18:22:08 +00:00
Commented Mar 5, 2020 at 18:22 — user2859458
– user2859458, Commented Mar 5, 2020 at 18:22

GMB · Accepted Answer · 2020-03-05 18:05:16Z

2

I think that you are looking for something along these lines:

DELETE FROM table_2 WHERE (name, value) IN (
    SELECT name, value FROM table_1
)

This searches and delete rows of table_2 whose name and value match (at least) a row from table_1.

If you have a query that gives you the list of (name, value) tuples to delete, you can just replace the subquery with that.

answered Mar 5, 2020 at 18:05

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2859458 Over a year ago

Is it possible to wildcard the WHERE (name, value) portion to something like WHERE (*)? My script doesn't readily have access to the table internals

GMB Over a year ago

@user2859458: I am unsure what you mean, but WHERE (*) IN (...) is not valid SQL.

Belayer Over a year ago

@user2859458: What do you mean by "doesn't readily have access to the table internals". If you can Select or in this case Delete, then you can access the table, unless there are column level access requirements (aka Row-Level Security).

user2859458 Over a year ago

Thanks for helping with this guys. The script I'm dealing with can run for multiple tables and is written to be schema agnostic. For inserts this isn't a problem: INSERT INTO table_name <row data> <--No schema data required. But I need to do a DELETE and was hoping there was a way to either pass rows to the WHERE clause or do some other kind of pattern matching to tell DELETE which row I want removed without explicitly notating table columns

Collectives™ on Stack Overflow

postgreSQL: DELETE by matching row data

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related