How to delete multiple rows in SQL Server

Question

I have a secondhand sale car database and four tables with relationships. Same column names have relationships.

Tables are:
Record: RecID Markname Model...
Features: FeatureID Featurename
Marks: MarkID Markname
Carfeature: CarfeatureID RecID FeatureID

Now, i want to delete a mark from Marks in c#. When i delete a mark, SQL must delete all records who has this mark.

I used a query like that:

DELETE from Carfeature 
 where RecID = (select RecID 
 from Record 
 where Mark = (select markname 
                 from Marks 
                where MarkID=@MarkID))";

string sorgudelmarkfromrecord = "DELETE from Record where Mark=(select Markname from Marks where MarkID=@MarkID)";

string sorgudelmark = "DELETE from Marks where MarkID=@MarkID";

When i run this, i get an error message like this:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

So, how can i delete multiple data from a table?

Answer 1

instead of using where RecID=(...) use where RecID in (...)

Answer 2

ON DELETE CASCADE on the foreign key?

Answer 3

If I understand you right, you can use this query to remove all CarFeatures for a cerain MarkID:

DELETE FROM CarFeature 
WHERE RecID IN (
    select RecID 
    from Record 
    where Markname IN (
        select Markname 
        from Marks 
        where MarkID = @MarkID
    )
)

The removal of records and marks is a simpler version of this query, I'll leave the exact SQL up to you.

Answer 4

use this

DELETE from Record r where r.Mark in (select m.Mark from Marks where r.MarkID=m.MarkID);

Note: markid must be primary key in both tables..