CODEWITHSUNDEEP
sqlsql-servert-sql
Mehmet Kaleli
Mehmet Kaleli

Reputation: 245

How to delete multiple rows in SQL Server

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?

Upvotes: 3

Views: 14636

Answers (4)

Imran Mujtaba
Imran Mujtaba

Reputation: 11

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..

Upvotes: 1

Andomar
Andomar

Reputation: 238048

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.

Upvotes: 11

Kris
Kris

Reputation: 41827

ON DELETE CASCADE on the foreign key?

Upvotes: 0

Moose
Moose

Reputation: 5412

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

Upvotes: 2

Related Questions