Delete the duplicate records from the sql table

Question

I have a scenario, I load my 1st file with file_id 8018 into stage table Then load my 2nd file with file_id 8019 into stage table.

There can be same records what we get in the 1st file.

In the result set i posted, we can see id in (2,3,4) records with file_id 8018 is in the 2nd file with file_id 8019

id  estimatename    market  spotlength  bcastweek   goaldollars file_id
2   3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-08  15499.75    8018
3   3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-15  15499.75    8018
4   3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-22  15499.75    8018
5   3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-24  32453.44    8018
58  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-08  15499.75    8019
59  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-15  15499.75    8019
60  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-22  15499.75    8019
62  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-08-05  15499.75    8019
63  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-08-12  15499.75    8019
64  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-08-19  15499.75    8019

I want my result set to be

id  estimatename    market  spotlength  bcastweek   goaldollars file_id
5   3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-24  32453.44    8018
58  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-08  15499.75    8019
59  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-15  15499.75    8019
60  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-07-22  15499.75    8019
62  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-08-05  15499.75    8019
63  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-08-12  15499.75    8019
64  3Q19 CALIFORNIA LOS ANGELES, CA :60 2019-08-19  15499.75    8019

I want to delete the records from 1st file when the same records exist in 2nd file.

Answer 1

One method is aggregation:

select max(id) as id, estimatename, market, spotlength, bcastweek, goaldollars,
       max(file_id) as file_id
from t
group by estimatename, market, spotlength, bcastweek, goaldollars;

If you have the two separate files, then you can also combine them using not exists:

select t2.*
from staging2 t2
union all
select t1.*
from staging1 t1
where not exists (select 1
                  from staging2 t2
                  where t2.estimatename = t1.estimatename and
                        t2.market = t1.market and
                        . . .
                 );

Answer 2

To present the results, a CTE can be used:

Tables:

Create Table #tbl1
(
id Int,
estimatename VarChar(10),
market VarChar(30),
spotlength VarChar(3),
bcastweek Date,
goaldollars Decimal(12,2),
[file_id] Int
)
Insert Into #tbl1 Values
(2,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-08',15499.75,8018),
(3,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-15',15499.75,8018),
(4,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-22',15499.75,8018),
(5,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-24',32453.44,8018),
(58,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-08',15499.75,8019),
(59,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-15',15499.75,8019),
(60,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-22',15499.75,8019),
(62,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-08-05',15499.75,8019),
(63,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-08-12',15499.75,8019),
(64,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-08-19',15499.75,8019)

Create Table #tbl2
(
id Int,
estimatename VarChar(10),
market VarChar(30),
spotlength VarChar(3),
bcastweek Date,
goaldollars Decimal(12,2),
[file_id] Int
)
Insert Into #tbl2 Values
(2,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-08',15499.75,8018),
(3,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-15',15499.75,8018),
(4,'3Q19','CALIFORNIA LOS ANGELES, CA',':60','2019-07-22',15499.75,8018)

CTE:

With cte As
(
Select 
       t1.*,
       t2.id As t2_id
From 
       #tbl1 t1 Left Join 
       #tbl2 t2 On t1.id = t2.id
Where 
       t2.id Is Null
)
Select * From cte

To perform the DELETE from Table 1:

Delete From #tbl1
From 
       #tbl1 t1 Left Join 
       #tbl2 t2 On t1.id = t2.id
Where 
       t2.id Is Not Null

Select * from #tbl1

Answer 3

--Try this sample and see if it helps you
CREATE TABLE #tmpstaging
(id int, 
estimatename  VARCHAR(100), 
market VARCHAR(100) , spotlength VARCHAR(100) ,bcastweek  VARCHAR(100) , goaldollars decimal(18,2) 
,file_id INT
)

INSERT INTO #tmpstaging(id,  estimatename ,   market , spotlength , bcastweek ,  goaldollars, FILE_ID) values
(2 ,  '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-08'  ,15499.75   , 8018)
,(3 ,  '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-15'  ,15499.75    ,8018)
,(4 ,  '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-22'  ,15499.75   , 8018)
,(5 ,  '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-24' ,32453.44   , 8018)
,(58 , '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-08'  ,15499.75    ,8019)
,(59 , '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-15' , 15499.75   , 8019)
,(60 , '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-07-22'  ,15499.75   , 8019)
,(62 , '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-08-05' , 15499.75   , 8019)
,(63 , '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-08-12' , 15499.75   , 8019)
,(64 , '3Q19','CALIFORNIA LOS ANGELES', 'CA :60' ,'2019-08-19'  ,15499.75    ,8019)





DELETE FROM #tmpstaging   WHERE id NOT IN 
(SELECT Max(id) FROM #tmpstaging
GROUP BY estimatename, market, spotlength ,bcastweek, goaldollars
 )
 SELECT * FROM #tmpstaging
DROP TABLE #tmpstaging