Delete * From (10000x rows) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Delete * From (10000x rows)

Author

Topic

bissa
Starting Member

32 Posts

Posted - 2012-05-08 : 11:11:33

I'm trying to clean my databse, and I want to delete some rows, I can delete it only row by row, is there a query to delete all the rows I want to delete same time?

Example:
I have random ID which I want to delete, I use this query to delete single row:
DECLARE @CharID INT;
SET @ID = 83242
DELETE FROM _Skill
WHERE (ID = @ID)
DELETE FROM _Mastery
WHERE (ID = @ID)
DELETE FROM _Guild
WHERE (ID = @ID)
This query delete 1 row, I 'm trying to delete specified rows which I have their ID, but do I have to write each ID seperately or there is a query to delete all rows same time?

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:25:09

you can put those ids onto a temporary table and then do delete by adding a join to it

like


CREATE TABLE #Del_Temp
(
ID int
)
INSERT INTO #Del_Temp (ID)
VALUES (83242),(11070),.... -- specify all id values here

--now do deletes as a batch
DELETE s
FROM _Skill s
INNER JOIN #Del_Temp d
ON s.ID = d.ID

DELETE m
FROM _Mastery m
INNER JOIN #Del_Temp d
ON m.ID = d.ID

DELETE g
FROM _Guild g
INNER JOIN #Del_Temp d
ON g.ID = d.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bissa
Starting Member

32 Posts

Posted - 2012-05-08 : 13:17:56

Thank you visakh16, worked fine!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 16:18:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources