-1

I need to delete entries in a join table where rows to be deleted are selected using aggregate selection of other tables (as asked here). Unfortunetly delete statement fails and MySQL/MariaDB throws the following error:

ERROR 1093 (HY000) at line 1: You can't specify target table 'pcld' for update in FROM clause

I thought that deleting data within a table based on select clause in the same table was supported.

MariaDB version is "mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64)"

SQL script

DELETE pcld 
FROM persons_linked_companies as pcld
WHERE pcld.id IN(
    SELECT 
        id
    FROM
        persons_linked_companies pcl
    WHERE
        companies_id = (SELECT 
                c.id
            FROM
                companies c
                INNER JOIN entities e ON e.company_id = c.id
                INNER JOIN persons p ON p.entity_id = e.id
            WHERE
                p.id = pcl.persons_id)
)

SELECT portion of this script is courtesy of nbk, see answer here

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
donmelchior
  • 893
  • 3
  • 13
  • 31

1 Answers1

1

I suppose you need to delete the records linked with persons

DELETE pcld 
FROM persons_linked_companies as pcld
JOIN companies c ON c.id = pcld.companies_id
INNER JOIN entities e ON e.company_id = c.id
INNER JOIN persons p ON p.entity_id = e.id
WHERE p.id = pcld.persons_id

In your original SQL, deleting/updating table cannot be opened again in the WHERE clause

ProDec
  • 5,390
  • 1
  • 3
  • 12
  • In fact I need to delete the records in the join table where persons are linked to their own company only, not all links between persons and companies, – donmelchior Oct 12 '21 at 13:52
  • not sure if `p.id = pcld.persons_id` means the linkage to the own company? The revised DELETE statement is rewritten according to your original SQL. – ProDec Oct 12 '21 at 13:54
  • You can replace the `DELETE pcld` with `SELECT pcld.*` to query to records and verify if they are meant to delete. – ProDec Oct 12 '21 at 13:57
  • A person is part of an entity, an entity is part of a company and the join table "persons_linked_companies" is created to describe a link between a person and a third party company. A person should not be linked to his own company so I need to delete this invalid data from a business logic perspective. Data structure (each table) is described here: https://stackoverflow.com/questions/69529471/how-to-select-entries-in-a-join-table-based-on-the-aggregate-selection-of-data-f – donmelchior Oct 12 '21 at 14:00
  • So in my understanding, the query is correct. If you are still not sure, you can test with real data. – ProDec Oct 12 '21 at 14:08