deleting rows in mysql

help with query

[pixel_juice]

I'm struggling to find the right way to formulate a mysql query. I want to do something like

Delete from thedatabase where field2 and field 3 on somerow have the same values as field2 and field 3 on someotherrow.

I can't figure out how to compare the data from two different rows.

Anyone have any ideas on how I can do this?

[Birdman]

Something like this maybe:

DELETE FROM table1 WHERE table1.field1 = table2.field1 AND table1.field2 = table2.field2

Should do the trick, I think.

[pixel_juice]

I'm not sure I understand, or I probably didn't explain it very well. The rows I need to compare are in the same table, so I need something like

DELETE FROM table1 WHERE row1.field1 = row2.field1 AND row1.field2 = row2.field2

but for every row.

Or maybe I'm appraoching this the wrong way?

[Birdman]

I follow now. What I usually do is select all records and then loop through them, looking for matches. If matches are found, then you can delete them.

Example:

$query = "SELECT id FROM table";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)){
$new_query = "DELETE FROM table WHERE field2 = '$row[field2]' AND field3 = '$row[field3]' AND id <> $row[id]";
if (mysql_query($new_query)) print "Deleted row #$row[id]!<br />";
}

Basically, it grabs all entries from the db, loops through them one at a time searching the db for other rows that have matching field2 and field3. Also, it will not delete the one being used as comparison. I assume you want to keep at least one entry, even when there are dupes.

For safety, I would test the script by printing the $new_query variable first.

$query = "SELECT id FROM table";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)){
$new_query = "DELETE FROM table WHERE field2 = '$row[field2]' AND field3 = '$row[field3]' AND id <> $row[id]";
print $new_query;
}

[coopster]

If your fields are keys and you are trying to eliminate duplicates, the MySQL REPLACE [mysql.com] command may be something to review.

[ergophobe]

[edit: syntax error corrected]

If you want to be safe and review records before deleting, this should work

SELECT *, count(*) AS rec_count FROM table_in_question GROUP BY field2, field3 HAVING rec_count > 1

That should return rows where those values are the same... I have to test it to see whether it returns all rows with the matching field.

EDIT:

Okay, so it only returns the first row that matches and a count of the number of rows. You'll then have to search for those values in other records and then you'll have your duplicates.

Tom