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)?
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>