CODEWITHSUNDEEP
sqlsql-serverselectsql-delete
Nimral
Nimral

Reputation: 697

SQL select into delete DIRECTLY

I need to select records, check my select criteria and enhance it until I got all records I need, and then delete (or otherwise change) those. Let's go with delete for the moment.

select <fields> from <database> where <condition>

(check, adjust condition)

delete from <database> where (<same condition>)

Isn't here an easier way, like pipes for instance, to shove the selected records directly into the delete statement? I used techniques selecting all record numbers into a temporary database, but isn't there an direct way?

I searched Stack Overflow, and found several questions where people ask how to convert a select statement into a delete statement, this is not what I want. What I have in mind is something like:

select ... from ... where ... | delete

or maybe something like this as a workaround ...

/* create a list of records to delete */
select ... from ... where ... into @temp

/* shove the list into delete */
delete @temp

Does the SQL standard support mechanisms for what I want, or is there maybe a platform specific approach (MS SQL Server)?

Upvotes: 2

Views: 5278

Answers (2)

Chris Pickford
Chris Pickford

Reputation: 8991

Given the fact it seems you are performing some kind of manual manipulation of your data, I would suggest wrapping anything you do in a transaction. This will allow you to see the results of your statement before making it permanent.

SELECT * FROM [table];

BEGIN TRAN

DELETE FROM [table] WHERE [predicate];
SELECT * FROM [table];

ROLLBACK TRAN
--COMMIT TRAN

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a Common Table Expression, like this:

;WITH CTE AS (
   select <fields>  
   from <table> 
   where <condition>
)
DELETE FROM CTE

This statement will delete all records returned by your query.

You can optionally add extra conditions applied to the in-line table returned by the CTE query, like:

;WITH CTE AS (
   select <fields>  
   from <table> 
   where <condition>
)
DELETE FROM CTE 
WHERE <more conditions on CTE fields>

Upvotes: 6

Related Questions