April 7, 2011 at 1:05 am
select a_id, a_datetime, b_id from
(select a.id as a_id, a.datetime as a_datetime, b.id as b_id from atest_detail b
left outer join
atest_header a
on a.id =b.id) dt
where dt.a_id is null
and dt.a_datetime is null
Based on above select query statement, how can I delete atest_detail, instead of select?
April 7, 2011 at 8:08 am
First thing is you might consider making your select statement a lot simpler
select a.id as a_id, a.datetime as a_datetime, b.id as b_id
from atest_detail b
left outer join atest_header a on a.id = b.id
where a.id is null
and a.datetime is null
Then you can just delete where the id is in your return like this
delete atest_detail
where id in
(
select b.id
from atest_detail b
left outer join atest_header a on a.id = b.id
where a.id is null
and a.datetime is null
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2011 at 2:17 pm
Test the execution plans but my testing shows this form of the query perform a bit better than the DELETE...IN method:
-- Credit to Itzik for exposure to technique
WITH cte
AS (SELECT *
FROM atest_detail b
LEFT OUTER JOIN atest_header a ON a.id = b.id
WHERE a.id IS NULL
AND a.datetime IS NULL
)
DELETE cte ;
My test code:
--Press Ctrl+M to include actual exec plans
IF OBJECT_ID(N'tempdb..#tmp') > 0
DROP TABLE #tmp;
GO
CREATE TABLE #tmp (id INT IDENTITY(1,1), NAME VARCHAR(10));
GO
INSERT INTO #tmp
(NAME)
VALUES ('name1'),('name2');
GO
SELECT * FROM #tmp;
BEGIN TRAN;
WITH cte
AS (SELECT *
FROM #tmp
WHERE NAME = 'name1'
)
DELETE FROM cte ;
SELECT * FROM #tmp;
ROLLBACK
DELETE #tmp
WHERE id IN (SELECT id
FROM #tmp
WHERE NAME = 'name1')
SELECT * FROM #tmp;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 8, 2011 at 2:22 pm
That's a pretty slick technique. Thanks for the idea.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2011 at 2:29 pm
I thought so too so I figured I would share it. The performance benefits are enough reason to add it to the toolkit. The fact that it reads a bit cleaner (to me) is a bonus.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 8, 2011 at 2:32 pm
No kidding. On both counts it is a definite improvement. I already added that snippet to my snippet box. I had to modify the credit comment slight of course. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply