asked on

delete a duplicate line or triplicate line and keep one

Delete duplicate items in table:

I have a table with a field called [LINEITEMID]

IT CONTAINS DUPLICATES

SELECT [PROJECTID]
      ,[SUBMITDATE]
      ,[STARTDATE]
      ,[AUDITEDDATE]
      ,[PROJECTTYPE]
      ,[PROJECTPROORRE]
      ,[ACCOUNTNUMBER]
      ,[NOOFLINEITEMS]
      ,[ACCOUNTTYPE]
      ,[SKU]
      ,[SEARCHSTATUS]
      ,[U_TYPE]
      ,[UPDATEDBY]
      ,[NOOFBOTHMFGPRESENT]
      ,[NOOFDESCRIPTION]
      ,[NOOFGRAINGERSKU]
      ,[NOOFMFGNAME]
      ,[NOOFMFGPARTNUMBER]
      ,[NOOFSPELLERRORMFGNAME]
      ,[AVGNOOFWORDSPERLINE]
      ,[ACCTTYPE]
      ,[PARENTTRACKCODE]
      ,[ADMINCONVERTEDDATE]
      ,[ADMINLINECONVERSION]
      ,[ADMINTIME]
      ,[ADMINRACFID]
      ,[COMPANYNAME]
      ,[CRMACCOUNTTYPE]
      ,[ADMINCONVERSIONTYP]
      ,[SLATYPE]
      ,[AUTOMATCH]
      ,[POSTCOUNTS]
      ,[MANUALMATCH]
      ,[EXCPETION_DIRECT]
      ,[AUDITEDBY]
      ,[ASSIGNEDBY]
      ,[PROJECTASSIGNTO]
      ,[QUALIFIEDLINES]
      ,[QUEUEACCOUNTTYPE]
      ,[QUEUENAME]
      ,[SEGMENT]
      ,[SELLERSRACFID]
      ,[SLADATE]
      ,[SPLITPROJECT]
      ,[STATUS]
      ,[TOTAL_EXPENSE]
      ,[BRANDNUMBER]
      ,[LASTMODIFIED]
      ,[LINEITEMID]
      ,[LINEITEMRESID]
      ,[MATCHEDROWID]
      ,[MFGNAME]
      ,[MFGPARTNUMBER]
      ,[MGRCODE]
      ,[POSTTYPESEARCH]
      ,[SALESSTATUS]
      ,[SUPPLIERNUMBER]
      ,[UPDTTYPE]
  FROM [Metrics].[dbo].[ORACLE_EXT]
  
  where [LASTMODIFIED] >= '2019-01-01'

Open in new window

What I need I need to dlete the duplicate line but keep one good ;line

The data looks like this

[LINEITEMID]
2234
2235
2236
2236
2237
2237
2237
2238
2239
2240...etc..

Thanks
fordraiders

David Todd

Hi,

An actual delete or just select one value?

One value is easy:

select distinct LineItemID
from Metrics.dbo.oracle_ext
;

HTH
David

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

A "duplicate" is defined BY (the whole) ROW being a repeat of some other row.

Otherwise what you show in your question is just repetition within a column WHICH MAY BE VALID

I would recommend taking great caution before deleting stuff based on just what we have seen so far.
For example what happens if the projectid is different?
[LINEITEMID] [projectid]
2234 1
2235 1
2236 1
2236 2

or any other column(s) being different?

Fordraiders

ASKER

portletpaul,
you are correct checking that now..

Fordraiders

ASKER

thanks