[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

SQl delete problem

Hello,

I posted a question earlier regarding a select problem.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24341643.html

In summary, I needed to get all rows that was duplicated on my table. solution found was:  (see code)
My problem now is,
I need to delete those duplicated rows. but I need to keep one record of each. because If i do a delete, all the duplicate will be gone lets say my table has columns:  tableID, passNumber, lineNo

well if it is:     100     10      1
                      100     10      1

a delete will delete both.    I need to keep one.  is that possible ?? (for all the different duplicates found)
select tableid, passnumber
from yourtable
group by tableid, passnumber
having count(*) > 1

Open in new window

0
PhilippeRenaud
Asked:
PhilippeRenaud
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
What is your primary key?
0
 
PhilippeRenaudAuthor Commented:
It would be TableID and PassNumber.

my problem is that the client gave me a list with duplicated rows and I created a table with the same columns (without primary keys otherwise it could not insert) and im trying to arrange that Table test correcly so after that I can put the good recrods into the good table..  are you following
0
 
JestersGrindCommented:
You could try something like this.

Greg



;WITH CTE 
AS
(
SELECT tableid, passnumber, ROW_NUMBER() OVER (PARTITION BY tableid, passnumber ORDER BY tableid, passnumber) AS ROW 
FROM yourtable
)
DELETE FROM CTE
WHERE ROW > 1

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
BrandonGalderisiCommented:
You are going to be stuck with looping since you don't have a key value outside of tableid and passnumber.
0
 
PhilippeRenaudAuthor Commented:
How then could I transfer the data to the good table having my primary keys.  otherwise I will have an error of duplicate
0
 
PhilippeRenaudAuthor Commented:
worked thank you
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now