Information About Deleting PostgreSQL

from the combination table below I would like to create SQL to delete the one with the same group ID as the A number.
I created SQL to extract, but I can't create SQL to delete.
I'd appreciate it if you could give me some advice.

Created SQL to extract.

SELECT
    Combination table.ID,
    Combination table.A number,
    Combination table.B number,
    InnerJoinTB1.Number,
    InnerJoinTB1.Group ID,
    InnerJoinTB2.Number,
    InnerJoinTB2.Group ID
from 
    COMBINATION TABLE
INNER JOIN 
    Number Table AS InnerJoinTB1 ON Combination Table .A Number = InnerJoinTB1. Number
INNER JOIN 
    Number Table AS InnerJoinTB2ON Combination Table .B Number = InnerJoinTB2. Number
WHERE
    InnerJoinTB1.Group ID!=InnerJoinTB2.Group ID

I have confirmed that the ID of the combination table I want to retrieve has been obtained.
I'm not sure how to build SQL to remove the appropriate records from the combination table...
I was wondering if I should simply select DELETE as the SELECT part, but it didn't work because it was INNER JOIN.

DELETE
from 
    COMBINATION TABLE
INNER JOIN 
    Number Table AS InnerJoinTB1 ON Combination Table .A Number = InnerJoinTB1. Number
INNER JOIN 
    Number Table AS InnerJoinTB2ON Combination Table .B Number = InnerJoinTB2. Number
WHERE
    InnerJoinTB1.Group ID!=InnerJoinTB2.Group ID

Number Table
ID Group ID Number
100 1000
2001 2000 300 23000 40034000

Combination Table
IDA Number B Number
1000 2000
23000 1000
34000 1000
42000 1000 A Number A and number B are tied to the number table

postgresql

2022-09-30 21:34

1 Answers

If the record is unique in the combination table .ID, it would be better to use the original SELECT statement as a subquery and specify the ID to be deleted.

/*Example*/
DELETE
from
    COMBINATION TABLE
WHERE
    Combination table.ID IN(
        SELECT combination table.ID
        from
            COMBINATION TABLE
        INNER JOIN
            Number Table AS InnerJoinTB1 ON Combination Table .A Number = InnerJoinTB1. Number
        INNER JOIN
            Number Table AS InnerJoinTB2ON Combination Table .B Number = InnerJoinTB2. Number
        WHERE
            InnerJoinTB1.Group ID!=InnerJoinTB2.Group ID
    )

Alternatively, if you want to use PostgreSQL extensions, you can use the using clause to specify the number table.(Reference: Description of PostgreSQL DELETE statements)

2022-09-30 21:34

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656