PostgreSQL: deleting rows referenced from another table

laurt

I have two tables, object and object_data, with object referencing object_data by foreign key (the relation is 1:1). For a set of objects, I need to null their object_data references and delete the corresponding object_data rows, like this:

DELETE FROM object_data WHERE id IN
( SELECT object_data_id FROM object WHERE ... );

UPDATE object SET object_data_id = NULL WHERE ...;

Problem is, the foreign key constraint doesn't allow deleting object_data rows that are still referenced from object.

My current solution is reading the results of the SELECT into a list, then nulling the foreign keys and then deleting the object_data rows in reasonable-sized batches using IN operator. Is there a better solution? Adding a column that refers back from object_data to object is not an option.

Eelke

Yes use CTEs (Common Table Expression)

WITH tmp AS (SELECT object_data_id FROM object WHERE ...),
    upd AS (UPDATE object SET object_data_id = NULL WHERE ...)
DELETE FROM object_data 
  WHERE id IN (SELECT object_data_id FROM tmp);

The first CTE called tmp is executed first and remembers the data you need later The second CTE called upd does sets the fields to NULL Finally the DELETE uses the data from tmp to perform the DELETE

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to delete from table then delete what deleted rows referenced? (postgresql)

From Dev

Efficiently deleting rows from one table where not matching another [MySQL]

From Dev

MySQL: deleting rows based on a condition with data from another table and NO JOIN

From Dev

Deleting multiple rows from a table

From Dev

Deleting multiple rows from a table

From Dev

Combine rows from self-referenced table

From Dev

Delete all rows in one table that aren't referenced by another table

From Dev

How to get referenced values from another table?

From Dev

PostgreSQL - insert rows based on select from another table, and update an FK in that table with the newly inserted rows

From Java

Deleting all rows from Cassandra cql table

From Dev

Deleting rows from 3 tables in MySQL table

From Dev

Issue deleting rows from displayed table

From Dev

PostgreSQL for each row from one table join all rows from another table

From Dev

Copy rows from table to another

From Dev

Select values from one table depending on referenced value in another table

From Dev

Checking certain row from a table which is referenced to another table?

From Dev

How to select elements from a table referenced by another table?

From Dev

Delete from one table unless row is referenced in another table

From Dev

Subtract rows of one table from another table

From Dev

Deleting rows in HTML table

From Dev

SQL Query: Deleting rows from PostgreSQL with same values

From Dev

How to update all rows of a table with an aggregate value referenced by foreign key on an another table in Entity Framework

From Dev

How does deleting rows from a table affect its indexes?

From Dev

Issue with Deleting rows from my second Table in my page

From Dev

c# deleting duplicate rows from data table

From Dev

Deleting all rows from table that share the same ID

From Dev

Deleting many rows from a big table MySql 5.5.46

From Dev

Deleting all rows from table, and resetting auto incrementation

From Dev

MySQL: Update rows in table, from rows with matching key in another table

Related Related

  1. 1

    How to delete from table then delete what deleted rows referenced? (postgresql)

  2. 2

    Efficiently deleting rows from one table where not matching another [MySQL]

  3. 3

    MySQL: deleting rows based on a condition with data from another table and NO JOIN

  4. 4

    Deleting multiple rows from a table

  5. 5

    Deleting multiple rows from a table

  6. 6

    Combine rows from self-referenced table

  7. 7

    Delete all rows in one table that aren't referenced by another table

  8. 8

    How to get referenced values from another table?

  9. 9

    PostgreSQL - insert rows based on select from another table, and update an FK in that table with the newly inserted rows

  10. 10

    Deleting all rows from Cassandra cql table

  11. 11

    Deleting rows from 3 tables in MySQL table

  12. 12

    Issue deleting rows from displayed table

  13. 13

    PostgreSQL for each row from one table join all rows from another table

  14. 14

    Copy rows from table to another

  15. 15

    Select values from one table depending on referenced value in another table

  16. 16

    Checking certain row from a table which is referenced to another table?

  17. 17

    How to select elements from a table referenced by another table?

  18. 18

    Delete from one table unless row is referenced in another table

  19. 19

    Subtract rows of one table from another table

  20. 20

    Deleting rows in HTML table

  21. 21

    SQL Query: Deleting rows from PostgreSQL with same values

  22. 22

    How to update all rows of a table with an aggregate value referenced by foreign key on an another table in Entity Framework

  23. 23

    How does deleting rows from a table affect its indexes?

  24. 24

    Issue with Deleting rows from my second Table in my page

  25. 25

    c# deleting duplicate rows from data table

  26. 26

    Deleting all rows from table that share the same ID

  27. 27

    Deleting many rows from a big table MySql 5.5.46

  28. 28

    Deleting all rows from table, and resetting auto incrementation

  29. 29

    MySQL: Update rows in table, from rows with matching key in another table

HotTag

Archive