delet distince data from table in php

Question

In these picture you can show one sql table in these table i have a coloum which name is "matrix_unique_id" in thst coloum i have many same id and in each id i store one image the image coloum name is image_path. Now i want to delete all the image in one unique id accept one image.For example: in these 6665682 id i have 25 images now i want to delete all images accept one means when execute the query it delete 24 images and left one.

what i do?

_“what i do?”_ – how about: Research …? https://www.google.com/search?q=mysql+delete+duplicates — CBroe, Jun 29 '17 at 08:19
see the accepted answer https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Jun Rikson, Jun 29 '17 at 08:29

Blank · Accepted Answer · 2017-06-30T07:18:50.147

0

You can use delete join:

delete t1
from yourtable t1
join yourtable t2
on t1.matrix_unique_id = t2.matrix_unique_id
and t1.id < t2.id

this will leave the max(id) of each matrix_unique_id.

Edit: If you want to leave min id, try to change

and t1.id < t2.id

to

and t1.id > t2.id

edited Jun 30 '17 at 07:18

answered Jun 29 '17 at 08:19

Blank

12,308
1
14
32

this is won't work since he have many same id in the table – Jun Rikson Jun 29 '17 at 08:25
sorry, I just saw his pic, this should works – Jun Rikson Jun 29 '17 at 08:28
@vinaykapoor Just table alias. – Blank Jun 29 '17 at 09:30
but when i execute these query one error occured – vinay kapoor Jun 29 '17 at 09:31
@vinaykapoor What is error? – Blank Jun 29 '17 at 09:33
it is work perfectlly ,data delete from table but what i do to delete the image from the folder where i store all my images. – vinay kapoor Jun 29 '17 at 09:41
@vinaykapoor Mysql can not delete the file on your local disk. – Blank Jun 29 '17 at 09:43
i know that but i write a php script when i run that script i want to delete image from database and also from my folder. – vinay kapoor Jun 29 '17 at 09:45
@vinaykapoor Then this is another question. – Blank Jun 29 '17 at 09:47
k nop.Can u explain me in these query how the join work – vinay kapoor Jun 29 '17 at 10:00
See https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql – Blank Jun 29 '17 at 10:02
@vinaykapoor Except reserved word, I think anything is OK. – Blank Jun 29 '17 at 10:12
"DELETE FROM rr2s4_cma_images WHERE matrix_unique_id='$matrix[$i]'"; i write these query or delete the data from table now i want to remain only one data which have minimum id than wt i do in these query?In these query first select distinct data from database and after that i delete the data but it delete all data from database. – vinay kapoor Jun 30 '17 at 07:03
@vinaykapoor See **Edit** part. And answer from Morgan is upvoted, why don't you just use his? :-| – Blank Jun 30 '17 at 07:19
Is it possible to delete all data except min id from database without using delete join? – vinay kapoor Jun 30 '17 at 07:28
You mean subquery? @vinaykapoor – Blank Jun 30 '17 at 07:30
yes if it is possible then give me solution about subquery – vinay kapoor Jun 30 '17 at 07:31
"DELETE FROM table_name WHERE matrix_unique_id='$matrix[$i]'"; I write these query but it delete all data from table – vinay kapoor Jun 30 '17 at 07:32
Any answer for that? – vinay kapoor Jun 30 '17 at 08:07
You accept answer then cancel accept, then accept again, now cancel accept again, please don't be so rude. By your such manner, I've lost patient about your question. @vinaykapoor – Blank Jun 30 '17 at 08:55
I really apologized for that i didn't do that ./my friend did it.Once again i said sorry. – vinay kapoor Jun 30 '17 at 12:47

delet distince data from table in php

1 Answers1