Deleting one row of similar rows

[ballouta]

Hello

 

I have two or more rows that contain the same data, only the ID differs.

e.g.:

 

ID      User               Filename                  Date            Received

 

112     almoudamer     _72200813059.ask    2008-07-02   N

115     almoudamer     _72200813059.ask    2008-07-02   N

140     almoudamer     _72200813059.ask    2008-07-02   N

 

I was using this query to delete the row according to some calculations, but i had a problem when i have three rows of the same user and the same file name, I need to delete ONLY one row.

	<?php
$query = "DELETE FROM `uploaded` WHERE `user` = '$user' AND `sentfile` = '$filename' ";
$result = mysql_query($query);
?>

 

thank you

[kishan]

DELETE FROM `uploaded` WHERE `user` = '$user' AND `sentfile` = '$filename'

 

HI,

You can delete the minimum/maximum one from the rows ....

 

DELETE FROM `uploaded` WHERE ID =(select min(ID) from 'uploaded' where  `user` = '$user' AND `sentfile` = '$filename')

 

DELETE FROM `uploaded` WHERE ID =(select max(ID) from 'uploaded' where  `user` = '$user' AND `sentfile` = '$filename')

[ballouta]

Hi

 

I tried this query but didn't delete anything, is this query correct?

 

<?php
$query = "DELETE FROM `uploaded` WHERE ID =(select min(ID) from 'uploaded' where  `user` = '$user' AND `sentfile` = '$filename') ";
$result = mysql_query($query);
?>

 

please Help

[TransmogriBenno]

The above SQL might work, but only if the DBMS supports subqueries in DELETE queries, and it will only delete one row at a time, which is probably not so great because you have 3 copies. I'd be wanting to delete two of them so that only one is left.

 

You obviously want to reference the row(s) that you want to delete by the ID. It's easy, if a little slower, to do two queries - one to find the record(s) you want to delete, and a second query to do the deletion. How about something like this, which would remove all duplicates:

 

1. find latest duplicate

SELECT MAX(ID) AS SaveID, User, Filename, Date, Received
FROM uploaded
GROUP BY User, Filename, Date, Received
HAVING COUNT(ID) > 1

 

2. remove prior duplicates

foreach ($row) {
  ...
  // make row values safe for insertion
  ...
  DELETE FROM uploaded WHERE ID != {$row['SaveID']} AND User = {$row['User']} AND ... // do equality check for all columns
  ...
}

[ballouta]

hi

the solution looks nice, but i am stucked in the first part of it,

the code that looks for duplicated is working:

<?php
$query = "SELECT MAX(ID) AS SaveID, user, sentfile, date, received
			FROM uploaded
			GROUP BY user, sentfile, date, received
			HAVING COUNT(ID) > 1 ";
?>

 

before i make any deletion I tried to output the result, I got an ID for duplicated rows (2 rows) but not the rows i am looking for! the rows i am looking : received = 'N', the query above found those where received ='Y'.

 

Do I have to add this 'where' in the above query?

 

Note that even if the table have many duplicated rows for several users, I am looking for a specific $user and specific $filename where received = 'N', when the code find it, it deletes ONLY one of them (if duplicated) BECAUSE when it does this process, the page will refresh and process the second duplicated, then refresh and deletes the third (for example)

if you are going to ask : why i am refreshing my page, the answer is that i am reading real emails using IMAP functions, each row has an email in the inbox for sure. the DB processes are according to these emails relevant to these dupliacted rows.

 

I am very sorry if I was not clear from the begining

 

thanks alot

 

[TransmogriBenno]

You would just add a where clause, then.

 

<?php
$query = "SELECT MAX(ID) AS SaveID, user, sentfile, date, received
			FROM uploaded
                                WHERE received = 'N' AND user = {$user} AND sentfile = {$filename}
			GROUP BY user, sentfile, date, received
			HAVING COUNT(ID) > 1 ";
?>

[ballouta]

i think this might be more easier for me:

 

<?php
$query = "SELECT * FROM `uploaded` WHERE `user` = '$user' AND `received` = 'N' AND `sentfile` = '$filename' ";
$result = mysql_query($query);
$row = mysql_fetch_array($result);?>

 

I will delete the row of $row[iD]... then the next refresh the code will fetch the second duplicated row and so on...

thank you