Solved

Drop matching rows in a query

Posted on 2010-08-23
7
326 Views
Last Modified: 2012-05-10
I have a query where I am matching 2 ID's. On a match I want to eliminate the row I am having a brain cramp here and can't remembr how to drop those rows. Thanks for the help.  
0
Comment
Question by:Wonderwall
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33506840
u want to return just unique rows? you could try adding distinct i.e.  select distinct ....
or do you want to show only records without duplicates?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33506847
How will you decide which one of the matches to keep OR are you wanting to delete both from the table? If you are just looking to select distinct, then you can try as above but since you said you are just matching on ID, I would suspect the other values of the row are unique and hence my question.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33506854
Delete tablea
where exists (
  select * from tableA B where tableA.ID=B.ID and B.uniquecolumn > tablea.uniquecolumn)

This gets rid of all extra records from tablea where the ID appears more than once.
Keeps the one with largest uniquecolumn.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Expert Comment

by:mcv22
ID: 33506864
DELETE t OUTPUT DELETED.* FROM table t WHERE ID IN (@ID1, @ID2)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33506876
If your select is

select * from tbl where ID in (123,345)

You can turn it into a DELETE

delete tbl where ID in (123,345)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33506887
if u wanting to remove the rows (I previously thought you meant dropping from display) then have a look at the ms approach http://support.microsoft.com/kb/139444
if its just for selecting, the other way is to use group by and having
select ...., count(*)
from ...
where ...
group by ...
having count(*)= 1
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33506900
I was thinking something like this for either:

;with t
as
(
   select *, row_number() over(partition by id order by some_column) rn
   from your_table_name
)
-- delete
select *
from t
where rn > 1;

This will show you the duplicates then you can comment out the "select *" line and uncomment the "delete" line.

Kevin
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now