1

I have a table in my DB where I keep track of user data. So I have rows like that:

(row_id,user_id, stat1,stat2,..., statN, timestamp)

Now I want to remove all duplicate records, where user_id and stat columns are the same, but only between other rows with different data. So if I have

(row_id, user_id, stat1, stat2, stat3, timestamp)
(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(3,1,1,1,1,3000)
(4,1,1,0,1,4000)

It should became

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(4,1,1,0,1,4000)

And not

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)

So I want to save record progression but remove meaningless duplicates.

  • Marco, thank you for markup, first time asking question here and from mobile :) – BlackSwan Sep 4 '18 at 8:34
0
delete t1.*
from `table` t1
inner join `table` t2
  on  t1.user_id = t2.user_id 
  and t1.stat1=t2.stat1
  and t1.stat2=t2.stat2
      /* ........... */  
  and t1.statN=t2.statN
  and t1.timestamp>t2.timestamp
left join `table` t3
   on t1.user_id = t3.user_id 
  and t3.timestamp > t2.timestamp 
  and t3.timestamp < t1.timestamp
where t3.user_id is null;

fiddle

  • Yay! Looks like exactly what I need! Will test it in few hours. Than you very much. – BlackSwan Sep 4 '18 at 9:00
  • @BlackSwan There can be a problem if the combination (user_id,timestamp) is NOT unique. – Akina Sep 4 '18 at 9:01
  • It should not be, but I will check for it, thanks – BlackSwan Sep 4 '18 at 9:04
  • @BlackSwan I do not know your task, but I think the uniqie index by the expression above may be usable... not only the check/deletion you need now. On the other hand it can affect on some soft causing errors, so be carefully. – Akina Sep 4 '18 at 9:06
  • Hm... Just put it in to phpMyAdmin on duplicate table of data set, it sez Error #1093, Table that changing t1 cannot be used in FROM – BlackSwan Sep 4 '18 at 9:20
0

So doing a join on the elements that constitute a duplicate and the last where criteria shows which to delete.

delete t1
from table t1, table t2
where t1.user_id = t2.user_id and t1.stat1=t2.stat1
     and t1.stat2=t2.stat2  and t1.stat3=t2.stat3
and t1.row_id+1=t2.row_id
  • Isn't it going to transform data in that way?(Which is unwanted behaviour) (row_id, user_id, stat1, stat2, stat3, timestamp) (1,1,1,0,1,1000) (2,1,1,1,1,2000) (3,1,1,1,1,3000) (4,1,1,0,1,4000) \/ (1,1,1,0,1,1000) (2,1,1,1,1,2000) – BlackSwan Sep 4 '18 at 8:45
  • why would it? Delete can only remove rows, it can't transform. Try on a test dataset to confirm this outcome. – danblack Sep 4 '18 at 8:46
  • I can't test it right now, but I believe you are missing the point of question. Look again at the question, please. I afraid that request will return result as in unwanted case with only two rows survive deletion. I need preserve duplicates that are separated from each other by rows that are different – BlackSwan Sep 4 '18 at 8:50
  • So and t1.row_id+1=t2.row_id as the final part of the query would solve this? – danblack Sep 4 '18 at 8:54
  • @danblack No guarantee of holes in row_id. Moreover, I cannot see guarantee of (t1.timestamp>t2.timestamp) => (t1.row_id>t2.row_id). – Akina Sep 4 '18 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.