Remove duplicate records based on record id, annoying GROUP BY statement

Hi GSquared, I was referring to the second set of requirements, and the problem with the proposed solution you provide (or at least from a quick scan of it) is that you will never delete the companyname row that has [Row]=1 - which is a possibility with these requirements. 🙂

MM, but if you're going to call someone out for missing something in the OP's post, it's helpful if you point out exactly what was missed. Saying "it's all in the post" doesn't help. Most people will still read what they read into the post the first time.

That's why I asked you to explain. Your non-response response just hit one of my pet peeves. Please don't do that again.

Brandie, I found your response to be curious - I was merely pointing out that you had not answered the question as asked - and I did not have time to go into the detail.

Now that I have more time - here are the requirements - perhaps you could explain what part you do not understand??

😉

or Row = 1

and exists (...another record for the same company that has rows in the sub-table...) and not exists (...row for this ID in the sub-table...);

Fill in the blank to fit whatever rules you like.

Actually, if this were "my" database, I'd probably do the whole thing completely differently. This isn't a straight-up dedupe. There's more data cleaning needed on this.

First, I'd look into consolidating all records for any given company name onto a single ID for each one. That's more important, most likely, than cleaning up empty records and pseudo-duplicates. So, if "ACME Inc" has 10 IDs, with the even numbered ones having records in the sub-table, I'd look at updating the sub-table to put all of those records onto the first ID for ACME. Then I'd run the original dedupe I proposed. Then I'd work out a unique index and some other tools that would prevent the data from getting dirty again. Anything less is, most likely, a half-solution.

@GSquared: its like you read the mind of my boss...

He said the exact same, and after thinking of it...it does make way more sense! 🙂

So the code Mr Magoo does exactly what my initial requirements were.

However, now Im left with duplicate records in my prospects table when those records have a reference to them from the pic table.

What I need is that the duplicate records in the prospects table are merged into 1, and the id of the record that remains in the prospects table is applied to all records in the pic table that had a reference to any of the duplicate records that are now merged....

Are we saying the same? If so, could you help me achieve this? Or you MrMagoo?

Thanks again!!!

Solving this would be a great christmas present....ok, thats kinda sad 😉

What you'd need to do would be find the first ID for each company name, and then update all the records in the subtable to match those.

For example:

;with PrimaryIDs (PrimaryID, Company) as

(select min(ID), company_name

from dbo.MyCompaniesTable

group by company_name),

IDs (PrimaryID, SecondaryID) as

(select PrimaryID, ID

from PrimaryIDs

inner join dbo.MyCompaniesTable

on PrimaryIDs.Company = MyCompaniesTable.company_name

and PrimaryIDs.PrimaryID < MyCompaniesTable.ID)

update Sub

set CompanID = PrimaryID

from dbo.MySubTable as Sub

inner join IDs

on Sub.CompanyID = IDs.SecondaryID;

What that does is find the first ID for each company, then find all the secondary IDs for the same company name, then update all the IDs in the subtable to match the "primary ID" for the company. Clear enough?

Once you've run that, then you use the same CTEs to delete from the companies table, thus getting rid of all of the duplicate companies.

Then you probably want to add a unique index to the company_name field, or find an better primary key than an ID value (there are lots of candidates for that), which will prevent duplicates from being put in the system in the first place.

Yes, its clear and it also ALMOST works...:)

my requirement is also that the records that will be deleted are inserted in another table (for backup purposes).

So, what I did is:

;with PrimaryIDs (PrimaryID, Company) as

(select min(ID), companyname

from prospects

group by companyname),

IDs (PrimaryID, SecondaryID) as

(select PrimaryID, ID

from PrimaryIDs

inner join prospects

on PrimaryIDs.Company = prospects.companyname

and PrimaryIDs.PrimaryID < prospects.ID)

update Sub

set prospectid = PrimaryID

from ProspectsInCampaigns as Sub

inner join IDs

on Sub.prospectid = IDs.SecondaryID;

;with DupeCheck as

(select row_number() over (partition by companyname order by ID) as Row, ID

from dbo.prospects)

delete from DupeCheck

output deleted.*

INTO prospects_copy

where Row > 1

But then I get:

"Column name or number of supplied values does not match table definition." on this line: ";with DupeCheck as"

What am I doing wrong?

UPDATE: I also tried replacing ID with *, so:

select row_number() over (partition by companyname order by ID) as Row, *

But that gives the same error...so my guess is now the Row column is causing the problem...but what I'd rather not have to do is individually insert the column names that are to be selected and inserted.

That is a strange request - if you don't want to manually enter all the column names, just open the object explorer, find your table and drag the "Columns" node to the query pane - all the columns are dumped into the query pane just ready for a select....

Otherwise, you would have to add a column for the row number to your backup table.

Sorry if I wasnt clear, but by this "what I'd rather not have to do is individually insert the column names that are to be selected and inserted.", I mean:

I want the statement to be flexible and if I later add columns to the database, I dont want to have to alter the query to include the newly added columns.

So thats why I want to use: *, instead of the actual column names.