SQL: complex delete
Basically I created several tables for the game: I have two main tables and a table with many of the many. Here is the DDL: (I am using HSQLDB)
CREATE TABLE PERSON
(
PERSON_ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
NAME VARCHAR(50), MAIN_PERSON_ID INTEGER
)
CREATE TABLE JOB
(
JOB_ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
NAME VARCHAR(50)
)
CREATE TABLE JOB_PERSON
(
PERSON_ID INTEGER,
JOB_ID INTEGER
)
ALTER TABLE JOB_PERSON ADD
CONSTRAINT FK_PERSON_JOB FOREIGN KEY(PERSON_ID)
REFERENCES PERSON ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE JOB_PERSON ADD
CONSTRAINT FK_JOB_PERSON FOREIGN KEY(JOB_ID)
REFERENCES JOB ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE PERSON ADD
CONSTRAINT FK_PERSON_PERSON FOREIGN KEY(MAIN_PERSON_ID)
REFERENCES PERSON ON DELETE CASCADE ON UPDATE CASCADE
insert into person values(null,'Arthur', null);
insert into person values(null,'James',0);
insert into job values(null, 'Programmer')
insert into job values(null, 'Manager')
insert into job_person values(0,0);
insert into job_person values(0,1);
insert into job_person values(1,1);
I want to create a delete statement that removes orphans from a JOB (if there is only one entry in the connection table in a specific job) based on PERSON.PERSON_ID.
In pseudo language:
delete from job where job_person.job_id=job.job_id
AND count(job_person.job_id)=1 AND job_person.person_id=X
Where X is person_id. I've tried many different ways; I think it is the "COUNT" part that is causing the problem. I am new to SQL so any help would be much appreciated.
source to share
I will not follow.
You cannot delete lines JOB
that have JOB_PERSON
lines (even one) due to your FK restrictions. So there is no way to delete rows JOB
based on rows PERSON
.
JOB_PERSON
must be removed before deleting JOB
or PERSON
.
If you want to delete all lines JOB
without JOB_PERSON
, then one of the ways:
DELETE FROM JOB
WHERE JOB_ID NOT IN (
SELECT JOB_ID
FROM JOB_PERSON
)
If you want to delete all rows JOB_PERSON
for a specific person, and then all orphans, do it in two steps:
DELETE FROM JOB_PERSON
WHERE PERSON_ID = X
DELETE FROM JOB
WHERE JOB_ID NOT IN (
SELECT JOB_ID
FROM JOB_PERSON
)
If you only want to delete the orphans JOB
previously associated with X, you will need to keep them in a temporary table until the first deletion.
INSERT INTO TEMP_TABLE
SELECT JOB.JOB_ID
FROM JOB
INNER JOIN JOB_PERSON
ON JOB_PERSON.JOB_ID = JOB.JOB_ID
WHERE JOB_PERSON.PERSON_ID = X
DELETE FROM PERSON
WHERE PERSON_ID = X
-- YOUR CASCADING DELETE DOES THIS:
/*
DELETE FROM JOB_PERSON
WHERE PERSON_ID = X
*/
-- Now clean up (only) new orphans on the other side
DELETE FROM JOB
WHERE JOB_ID NOT IN (
SELECT JOB_ID
FROM JOB_PERSON
)
AND JOB_ID IN (
SELECT JOB_ID
FROM TEMP_TABLE
)
source to share