How can I remove duplicate records in MySQL while keeping those data fields in the duplicated item but not in the original item?
I have several thousand records with multiple 100 fields in a MySQL table.
Some of the records are duplicates and marked as such. Now that I can just remove the cheats, I want to keep any other possible valuable non-null data that is not in the original version of the record. Hope I made sense.
For example:
a b c d e f key dupe
--------------------
1 d c f k l 1 x
2 g h j 1
3 i h u u 2
4 u r t 2 x
In the above example table, the desired output is:
a b c d e f key dupe
--------------------
2 g c h k j 1
3 i r h u u 2
If you look closely at it, the duplicate is identified by the key (it's the same for 2 records, so the one that has the "x" field to cheat is the one to remove, keeping some of the fields from cheating (like the values c, e for key 1).
Please let me know if you need more information on this cryptic issue.
Thanks a ton!
ps: If this is not possible using MySQL, a PERL / Python script sample would be awesome! Thank!
source to share
import string, os, sys
import csv
import MySQLdb
import pickle
from EncryptedFile import EncryptedFile
enc = EncryptedFile( os.getenv("HOME") + '/.py-encrypted-file')
user = enc.getValue("user")
pw = enc.getValue("pw")
db = MySQLdb.connect(host="127.0.0.1", user=user, passwd=pw,db=user)
cursor = db.cursor()
cursor2 = db.cursor()
cursor.execute("select * from delThisTable")
rows = cursor.fetchall()
data = dict()
for row in rows:
key, primary = row[0], row[1]
if key not in data:
data[key] = list(row[:-1])
else:
for i in range(len(row)-1):
if data[key][i] is None or (not primary and row[i] is not None):
data[key][i] = row[i]
See Messa's answer in this thread ...
source to share
If I understand you correctly, you need to 1) merge 2 records 2) delete the marked records (it's simple).
For the first task, you can use something like
UPDATE table1 t1
INNER JOIN table1 t2 ON (t1.key = t2.key AND t2.dupe='x')
SET t1.b= IFNULL(t1.b,t2.b), t1.c=IFNULL(t1.c,t2.c), ...etc
WHERE t1.dupe IS NULL
PS This query assumes that any record has 0 or 1 duplicates; if you have more, the request needs to be changed.
source to share
Using code, you can concatenate the strings and then remove the cheat.
if you want to stay inside the sql code you need to run a stored procedure on every column of the table which update the original row only if it is null and the destination row is not null.
iteration of all pairs of covers using some kind of cursor.
This is a general idea, I hope someone here can help you with more specific code ...
source to share
The answer very much depends on what you actually want to do. Do you want to duplicate records in place, or you want to create a temporary table with the desired result. If you want to edit posts in-place, I think you'd be better off using python to do multiple sequential requests like a1ex07 for example, and then just "remove from ... where dupe = 'x". If you can use a temporary table, you can use one (not so) simple mysql "INSERT INTO ... SELECT", concatenating the dupe records with IFNULL or CAOLESCE and the actual igoning records with dupe = 'x'
source to share