I have three tables and they are related in some ways by connecting on specific IDs

The 3 tables are:

• Table CMD with ID as the key
• Table CMI with MeteringID as PK, and ID column referencing CMIH.IntervalID column
• Table CMIH with IntervalID as the key

I would like to delete data from CMD, CMI and CMIH tables. Where I can put let say CMD table has Date field to where CMD.Date is xyz... to filter down

This is my SQL statement:

select * 
from Table1 as CMD
join Table2 as CMI on CMD.id = CMI.MeteringID
join Table3 as CMIH on CMI.ID = CMIH.IntervalID

Thank you in advance everyone.

CodePudding user response：

DELETE *
FROM CMD
WHERE (ID = @ID)

DELETE *
FROM CMI
WHERE (MateringID = @ID)

DELETE * 
FROM CMIH
WHERE (IntervalID = @ID)

You can delete the data in these 3 tables by sending the @ID as a parameter.

CodePudding user response：

DELETE FROM Table3 Where IntervalID=@ID

DELETE FROM Table2 Where MeteringID=@ID

DELETE FROM Table1 Where id=@ID Try This

CodePudding user response：

You can use TSQL to handle cascading deletion.

delete CMD
from Table1 as CMD
join Table2 as CMI on CMD.id = CMI.MeteringID
join Table3 as CMIH on CMI.ID = CMIH.IntervalID

delete from Table2 where MeteringID not in (select id from Table1)
delete from Table3 where IntervalID not in (select id from Table2)

CodePudding user response：

DELETE *
FROM CMD
WHERE (ID = @ID)

DELETE * 
FROM CMIH
WHERE IntervalID in (select ID from FROM CMI
WHERE MateringID = @ID)

DELETE *
FROM CMI
WHERE (MateringID = @ID)

You can delete the data in these 3 tables by sending the @ID as a parameter.