delete multiple rows across multiple tables

Question

I have something like the following set of tables in a DERBY db, where a PAIR row represents multiple rows in the PERSON table, a SUBGROUP represents multiple rows in the PAIR table, and a SUPERGROUP represents multiple rows in the SUBGROUP table.

<table>
        <name>SUPERGROUP</name>
        <column>
            <name>SUPERGROUPID</name>
        </column>
</table>


<table>
        <name>SUBGROUP</name>
        <column>
            <name>SUBGROUPID</name>
        </column>
        <column>
            <name> SUPERGROUPID </name>
        </column>
    </table>


<table>
        <name>PAIR</name>
        <column>
            <name>PERSONID1</name>
        </column>
        <column>
            <name>PERSONID2</name>
        </column>
        <column>
            <name> SUPERGROUPID </name>
        </column>
        <column>
            <name> SUBGROUPID </name>
        </column>
    </table>


<table>
        <name>PERSON</name>
        <column>
            <name>PERSONID</name>
        </column>
        <column>
            <name> SUPERGROUPID </name>
        </column>
        <column>
            <name> SUBGROUPID </name>
        </column>
    </table>

I want to be able to delete an SUBGROUP row and all entries in the PAIR table and PERSON table that are in that SUBGROUP and the SUBGROUP's SUPERGROUP (don't want to delete rows if the same subgroup id exists in a different supergroup).

I've tried to do an Inner join described in previous posts like the following but SQuirrel SQL is giving an SQLState:42X01 ErrorCode:30000 syntax error on the command after SUBGROUP.

DELETE FROM
  SUBGROUP,
  PAIR,
  PERSON USING SUBGROUP
  INNER JOIN PAIR
  INNER JOIN PERSON
WHERE
  SUBGROUP.SUBGROUPID = '1'
  AND SUBGROUP.SUBGROUPID = PAIR.SUBGROUPID
  AND SUBGROUP.SUBGROUPID = PERSON.SUBGROUPDID
  AND SUBGROUP.SUPERGROUPID = 'S1'
  AND SUBGROUP.SUPERGROUPID = PAIR.SUPERGROUPID
  AND SUBGROUP.SUPERGROUPID = PERSON.SUPERGROUPID

Is the above correct? Is there a better way to do it?

Also, is there a way to create a single SQL statement if I have multiple SUBGROUPs that I want to delete. ie. I want to delete SUBGROUPs {1,2,3,4,7,10} in SUPERGROUP S1?

Today I'm deleting one row at a time across all the tables but this is taking far too long when deleting 1000s of SUBGROUPs.

[possible duplicate](https://stackoverflow.com/questions/15468505/delete-from-two-tables-in-one-statement) — m4gic, Aug 31 '18 at 20:44

score 1 · Answer 1 · answered Feb 13 '19 at 12:39

I think the solution you're looking for is :

DELETE
  SUBGROUP T1,
  PAIR T2,
  PERSON T3
FROM T1
  INNER JOIN T2 ON (T1.SUBGROUPID = T2.SUBGROUPID AND T1.SUPERGROUPID = T2.SUPERGROUPID)
  INNER JOIN T3 ON (T1.SUBGROUPID = T3.SUBGROUPDID AND T1.SUPERGROUPID = T3.SUPERGROUPID)
WHERE
  T1.SUBGROUPID = '1'
  AND T1.SUPERGROUPID = 'S1'

delete multiple rows across multiple tables

1 Answers1