CODEWITHSUNDEEP
sqlsql-server
Crezzer7
Crezzer7

Reputation: 2335

SQL Server - Delete (x) number of rows depending on a WHERE caluse

I have a scenario where throughout the day, records are set to delete (BOOLEAN VALUE), but are not actually deleted. A list of these values is displayed within a view (let's call it ViewToDelete), for example:

ID      DELETE
--------------
1         1
4         1
5         1

At the end of the day I want to be able to delete these values present in this view all in one go, can this be achieved and if so how?

DELETE FROM mytablename 
WHERE ID = ViewToDelete.ID 

--THIS WOULD WORK IF THERE WAS 1 VALUE, BUT FOR MULTIPLE VALUES
--IT RETURNS AN ERROR AS I WOULD EXPECT

Upvotes: 0

Views: 84

Answers (2)

Anurag Das
Anurag Das

Reputation: 11

You can simply write

Delete from Viewtodelete where delete=1

If the view fetches all its data from only 1 table.

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can do this by JOINing to the view:

Delete  M
From    MyTableName     M
Join    ViewToDelete    V   On  V.Id = M.Id
                            And V.Delete = 1

Upvotes: 7

Related Questions