Link to home
Start Free TrialLog in
Avatar of twittoris
twittoris

asked on

Adding an index and deleting duplicates

I have a table that I have been joining data on. There are duplicate entries in the database. Do I have to add an index in order to delete duplicate entries?
What SQL command should I run?
The table coloumns are :
BLOCK
LOT
ADDRESS
OWNER
ENTITY_M
ENTITY_ADD
Avatar of marklogan
marklogan
Flag of United Kingdom of Great Britain and Northern Ireland image

Which column is the unique identifier?

One possible solution is to create an empty copy of the table and run an INSERT SELECT statement.

Then delete your old table and rename the new one.

You can also set a UNIQUE Key on the new table which will stop you entering duplicate rows again.


INSERT INTO newTable SELECT Block, Lot, Address, Owner, Entity_M, Entity_Add FROM duplicateTable GROUP BY ColumnNameToPickOutDuplicates.

Open in new window

Avatar of twittoris
twittoris

ASKER

hmmm what if i want every column to match in order to be grouped? Not just duplicates for the column Address.
So the unique record is made up by all the columns combined?

You can group by all the columns.
SELECT

.....


GROUP BY
 BLOCK,
 LOT,
 ADDRESS,
 OWNER,
 ENTITY_M,
 ENTITY_ADD

Open in new window

Right, I had duplicates in a master table i used and the database populated the duplicates with information. So I need to get rid of/permanently group the rows that have duplicate entries.
ASKER CERTIFIED SOLUTION
Avatar of marklogan
marklogan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Guy Hengel [angelIII / a3]
distinct and group by issues are discussed here: https://www.experts-exchange.com/A_3203.html