Solved

Drop matching rows in a query

Posted on 2010-08-23
7
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 60

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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