SELECT then DELETE versus extra clause in SELECT
- From: metaperl <metaperl@xxxxxxxxx>
- Date: Thu, 29 Nov 2007 07:12:59 -0800 (PST)
Far below (in section "original 3 steps"), you see the following:
1. a temp table is created
2. some data is inserted into this table
3. some of the inserted data is removed based on a join with the same
table that the original select was made from
In my opinion, there is no way that the join could produce more rows
than were originally retrieved from viewD. Hence, we could get rid of
the DELETE step by simply changing the query to be:
INSERT INTO #details ( rec_id, orig_corr, bene_corr )
SELECT rec_id, 0, 0
FROM viewD
WHERE SOURCE_SYS NOT IN ( 'G', 'K' )
AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )
AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )
AND INMESS NOT LIKE '2__' ---- the added line
===== original 3 steps (mentioned above) =====
CREATE TABLE #details (
rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
orig VARCHAR(35) NULL,
bene VARCHAR(35) NULL,
orig_corr TINYINT NULL,
bene_corr TINYINT NULL
)
INSERT INTO #details ( rec_id, orig_corr, bene_corr )
SELECT rec_id, 0, 0
FROM viewD
WHERE SOURCE_SYS NOT IN ( 'G', 'K' )
AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )
AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )
DELETE d
FROM #details d
JOIN viewD v ON ( d.rec_id = v.rec_id )
WHERE INMESS LIKE '2__'
.
- Follow-Ups:
- Re: SELECT then DELETE versus extra clause in SELECT
- From: Erland Sommarskog
- Re: SELECT then DELETE versus extra clause in SELECT
- Prev by Date: Re: SQL Express - Identity specification property - how to change
- Next by Date: Re: Distinct Name with Date Order By Date
- Previous by thread: Distinct Name with Date Order By Date
- Next by thread: Re: SELECT then DELETE versus extra clause in SELECT
- Index(es):