September 16, 2008 at 8:45 am
Ok so I have three tables that have the following scheme and am having trouble figuring out how to write my delete to remove records that exist in one table but not the other without wiping out other records that should not be deleted. Heres my scheme:
Table A
pk AID
fk fkey1ID
fk fkey2ID
Table B Table C
pk BID fk AID
fk AID
Now fkey1ID in Table A basically represents an account(parent relationship) and AID(children). So in Table B & C we have multiple AIDs related to one fkey1ID.
Table B turns out to be a subset of Table C. So what I need to do is figure out how to remove records from Table C that don't exist in Table B by fkey1ID in Table A. Im doing this in a stored procedure. So I know from Table A what all my AIDs are for any fkey1ID but how do I compare them between Table B and C to say if not in Table B delete in Table C?
J
September 16, 2008 at 9:31 am
J,
See if this does the trick...or maybe at least help you in the right direction, if I messed up the logic somewhere...
Left joining to table B and then eliminating rows that find a match, which should only leave you with the values that are not in table B.
Then joining to C on those values to Delete them.
delete from tableC
from tableA
left outer join TableB on tableA.fkey1ID = TableB.fkey1ID
join tableC on tableA.fkey1ID = tableC.fkey1ID
where tableB.fkey1ID is null
Cheers,
K
September 16, 2008 at 12:32 pm
Delete C
From TableC As C
Inner Join TableA As A
On (C.AID = A.AID)
Where A.fkey1ID = 'Your fkey1 Filter'
And Not Exists (
Select 1
From TableB As B
Where B.AID = C.AID
)
September 16, 2008 at 12:59 pm
Be sure you check the logic as a SELECT first.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply