CODEWITHSUNDEEP
sqloracle-database
sreekem bose
sreekem bose

Reputation: 471

delete from table according to a conditon in sql

I have tables xx_org , xx_people and xx_asg. the org_id is common between xx_asg and xx_org and asg_id is common between xx_people and xx_asg.

Now i want those organizations to be deleted from xx_org whose org_id is not in xx_asg and the person id in xx_people who have a column value extra_info as 'ITEM_OTB'. 

XX_ORG                 XX_PEOPLE                        XX_ASG

ORG_ID                 PERSON_ID                        ASG_ID
ORG_NAME               PERSON_NAME                      ASG_NAME
LEGAL_ENTITY           PERSON_NUMBER                    ORG_ID
                       ASG_ID
                       EXTRA_INFO

DELETE FROM XX_ORG WHERE 
ORG_ID NOT IN (SELECT DISTINCT ORG_ID FROM XX_ORG);

How do i incorporate my second requirement in this one that is thsoe people from xx_person who do have extra_info as 'ITEM_OTB' , Those people's org ids should also be deleted

Upvotes: 0

Views: 49

Answers (1)

Ted
Ted

Reputation: 4057

If I understood you correctly, we want to delete all orgs except those that exist in asg who have people with extra info equal to ITEM_OTB.

DELETE  
FROM        XX_ORG
WHERE       ORG_ID NOT IN (
                SELECT      DISTINCT a.ORG_ID
                FROM        XX_ASG a
                            INNER JOIN XX_PEOPLE p ON a.ASG_ID = p.ASG_ID
                WHERE       p.EXTRA_INFO = 'ITEM_OTB');

Upvotes: 1

Related Questions