Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
OK. I have a table that with some duplicate records (Rows).

Example: employeeID 1001 is twice in the table with every column having the same data except ColumnX is different. So it looks like this. ColumnX shows blank and other one show 7 in the table.

How can I ONLY delete the one that is blank?

1001 ColumnX

1001 7

Updated 1-Feb-16 15:10pm
Sergey Alexandrovich Kryukov 1-Feb-16 21:14pm    
Why not preventing duplicates in first place, when the data is added?
Member 11829482 1-Feb-16 21:49pm    
I am putting the raw data into temp table and doing some updates of the fields which is working fine except the issue above.
Philippe Mori 11-Feb-16 22:18pm    
Well, the best solution to the problem avoid it as SA mentionned. You should always try your code on a copy of a database first and if the code does not work as intended, fix the code and if it works, then run it on the production database.

If you have already messed up the database, then it is up to you to write code that find those extraneous row and delete them. But try your code on a copy to ensure that the code works as intended.
Member 11829482 1-Feb-16 21:52pm    
in addition, the way that data is, after doing some updates to the temp table, I am getting what I need but need to get rid of the above issue,
PIEBALDconsult 1-Feb-16 22:19pm    
How about DELETE FROM table WHERE employeeID=1001 AND ColumnX IS NULL ?

This article might be helpful for you to delete duplicate rows
Share this answer
you can delete duplicate records using cte.
Refer below post
delete duplicate records in sql server

Share this answer
-- A cut down version of the problem.
create table duplicates
 id varchar(10),
 colX varchar(10) null

insert into duplicates (id, colX) values ('1001','7')
insert into duplicates (id, colX) values ('1001',null)
insert into duplicates (id, colX) values ('1001',null)
insert into duplicates (id, colX) values ('1002','8')
insert into duplicates (id, colX) values ('1002',null)
insert into duplicates (id, colX) values ('1003','9')

And to identify rows to delete....

-- Identify duplicate ID values
-- Using a table variable but it could be a temp table.
declare @duplicateKey table (key1 varchar(10))

insert into @duplicateKey (key1)
select id from duplicates group by id having count(id) > 1

Now check we have only 1001, 1002 in our list of deletion candidates.

select * from @duplicateKey 

(2 row(s) affected)

-- Delete rows with identified IDs and "invalid" columns 

delete duplicates
from duplicates 
inner join @duplicateKey as deleteList
      on deleteList.key1 = 
      and duplicates.colX is null

-- Prove that we have only "valid" rows.
select * from duplicates

id         colX
---------- ----------
1001       7
1002       8
1003       9

However as was pointed out above you're better off preventing duplicates in the first place if at all possible.
Share this answer
>> Select the distinct records into a new table
>> Truncate the old table
>> Merge the new table into the old table
Share this answer
Do you think something like this would be sufficient?

-- Lets say your table is called lala and has the following values:
create table lala (employeeID int, colx int)
insert into lala values (1001, null)
insert into lala values (1001, 7)
insert into lala values (1001, 8)
insert into lala values (1002, null)
insert into lala values (1002, 7)
insert into lala values (1002, 9)
insert into lala values (1002, 10)
insert into lala values (1003, 11)
insert into lala values (1004, null)
insert into lala values (1005, 12)
insert into lala values (1005, 20)
insert into lala values (1005, 21)
insert into lala values (1005, 22)
insert into lala values (1006, 24)
insert into lala values (1006, null)

-- Now you can define a second table to do the job:
create table #lala2 (employeeID int, colx int, inc int identity(1, 1))
insert into #lala2 (employeeID, colx)
select employeeID, colx from lala
order by colx desc -- if you want to delete the records with colx=null

-- Delete the multiple occurrencies and leave just one.
delete from #lala2
where exists (select 1
                from #lala2 maxinc
			   where maxinc.employeeID = lala2.employeeID
			     and >

delete from lala -- Delete old values
insert into lala select employeeID, colx from #lala2 -- Insert new values

drop table #lala2 -- Drop temp table
Share this answer
Member 11829482 2-Feb-16 21:29pm    
need to delete one of the double EmployyeID row where colx is NULL

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900