Change Duplicated records (1 Viewer)

mansied

Member
Local time
Today, 11:41
Joined
Oct 15, 2020
Messages
99
Hello
I need your help,
I have a table in which some records have been duplicated .based on one field ( ex. Id ) I need to change the duplicated Id ( ex . add -1 at the end of it )
how can I do it in a query? how can ask to find the duplicated records and change the id ?
thank you in advance
 

plog

Banishment Pending
Local time
Today, 10:41
Joined
May 11, 2011
Messages
11,613
First, this query will identify "duplicate" ID numbers--it will not change any data:

Code:
SELECT ID, COUNT(ID) AS RecordCount
FROM YourTableNameHere
GROUP BY ID
HAVING COUNT(ID)>1 
ORDER BY COUNT(ID)


Lots of questions:

1. Are you sure you have just one "duplicate" per ID? If you have 3 that means 2 records are going to get the "-1" suffixed to it and thus more "duplicates". The query above will show you the record counts to verify this.

2. Is this a 1 time thing, or are more "duplicates" going to get in? If this will happen in the future then you need to rethink your process.

3. Which record is the "duplicate"? Does it matter? You say they are duplicate on ID, which implies other field values may be different--if they are not entirely duplicate which one is the "duplicate"?

4. Can you provide some sample data and identify a duplicate and what you expect to happen? If you could upload your table that would be best.
 

mansied

Member
Local time
Today, 11:41
Joined
Oct 15, 2020
Messages
99
First, this query will identify "duplicate" ID numbers--it will not change any data:

Code:
SELECT ID, COUNT(ID) AS RecordCount
FROM YourTableNameHere
GROUP BY ID
HAVING COUNT(ID)>1
ORDER BY COUNT(ID)


Lots of questions:

1. Are you sure you have just one "duplicate" per ID? If you have 3 that means 2 records are going to get the "-1" suffixed to it and thus more "duplicates". The query above will show you the record counts to verify this.

2. Is this a 1 time thing, or are more "duplicates" going to get in? If this will happen in the future then you need to rethink your process.

3. Which record is the "duplicate"? Does it matter? You say they are duplicate on ID, which implies other field values may be different--if they are not entirely duplicate which one is the "duplicate"?

4. Can you provide some sample data and identify a duplicate and what you expect to happen? If you could upload your table that would be best.
Thanks for your reply
1- I have just one duplicated ..( It seems the records are different but the important and unique field has been duplicated )
1611938539550.png


as you see the Availibility ID is diffrent and unique.
i just need to add "-1"add the end of duplicated task_id field

Thanks
 

plog

Banishment Pending
Local time
Today, 10:41
Joined
May 11, 2011
Messages
11,613
1- I have just one duplicated

How so? I see 2 pairs of records with the same TASK_ID value. Also, what about my 3 other questions?
 

mansied

Member
Local time
Today, 11:41
Joined
Oct 15, 2020
Messages
99
How so? I see 2 pairs of records with the same TASK_ID value. Also, what about my 3 other questions?


I need to run it one time .to update the second records with task_ID_1 to make task Id be unique.
I have just Copy all duplicated records in a table which are about 800 records.

ex:
N/A-NJA_2016-05_01163
N/A-NJA_2016-05_01163-1
 
Last edited:

plog

Banishment Pending
Local time
Today, 10:41
Joined
May 11, 2011
Messages
11,613
I can't help you unless you discuss things with me, not just post unrelated ideas.
 

Users who are viewing this thread

Top Bottom