I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the master table, but reference other tables also. The 200 values in the master table are spread out in other 9 tables.
My question is I want to find out if any of the 200 values are present in all the 10 tables and delete them.
Suppose the master table has id =1 and this record needs to be deleted. Apart from deleting the value in this table, I need to check the remaining 9 tables whether this value is present or not and delete those records also.
How can I do this?
DELETE ... USING ...
joins, starting at the "ends" of the FK chains, then working your way back up. This could be automated with some PL/PgSQL logic that queriesinformation_schema
if there are too many to do manually. – Craig Ringer Jun 20 '15 at 0:39