Delete 1 that has 2 of the same ids

Transact-SQL
1

hi

i have the following select statement that pulls back the records i want. but there are duplicate records based on field [Instr_nsaddr] i just want to delete one of them out doesnt matter which one. whats the best way to do it


Select 
Fund_Fund_Code,
[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
[Instr_nsaddr],
[Portfolio_PB Legal Eyntit],
[Tax Lot_Quantity1] = sum([Tax Lot_Quantity])
from dbo.HegdeServWorkingTaxLotTableName
where [Instr_Expiry Date] = [Instr_Expiry Date]
and Portfolio_Clearer = Portfolio_Clearer
and Instr_Instrument = Instr_Instrument
and Instr_nsaddr = Instr_nsaddr
and [Portfolio_PB Account Number] = [Portfolio_PB Account Number]
and [Tax Lot_Transaction Code] = 'Buy' or [Tax Lot_Transaction Code] = 'SellShort'
 group by Fund_Fund_Code,[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
[Instr_nsaddr],
[Portfolio_PB Legal Eyntit]
having sum([Tax Lot_Quantity]) = 0
2

Maybe Select Distinct ?

3

dont think it will work but not sure

i tried this



delete from dbo.HegdeServWorkingTaxLotTableName where [Instr_nsaddr] not in 
(
Select 
Fund_Fund_Code,
[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
min([Instr_nsaddr]),
[Portfolio_PB Legal Eyntit],
[Tax Lot_Quantity1] = sum([Tax Lot_Quantity])
from dbo.HegdeServWorkingTaxLotTableName
where [Instr_Expiry Date] = [Instr_Expiry Date]
and Portfolio_Clearer = Portfolio_Clearer
and Instr_Instrument = Instr_Instrument
and Instr_nsaddr = Instr_nsaddr
and [Portfolio_PB Account Number] = [Portfolio_PB Account Number]
and [Tax Lot_Transaction Code] = 'Buy' or [Tax Lot_Transaction Code] = 'SellShort'
 group by Fund_Fund_Code,[Fund_Client_Legal_Name],
[Fund_Fund_Base_CCY],
[Fund_Fund_Legal_Name],
[Report_End Date],
[Tax Lot_Tax Date],
[Instr_Expiry Date],
[Instr_DM_Type],
[Instr_CCY],
[Portfolio_Clearer],
[Instr_Instrument],
[Instr_nsaddr],
[Portfolio_PB Legal Eyntit]
having sum([Tax Lot_Quantity]) = 0 )

but go the following error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error?

4

Can you add a test to the main table, make all 'Y' and just change the additional record to 'N', then you can select excluding 'N'

5

I mentioned this in a previous thread that you (IIRC??) posted.

What are you expecting that this will do?

All these tests are TRUE for ALL rows ...

6

To delete duplicate rows from a table:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187510