I need to search for duplicates - table as follows:
Code:
mysql> SELECT
	COUNT(*) c,
	theTel,
	theDate
FROM
	`doTable`
GROUP BY
	theTel,
	theDate
HAVING
	c > 1;

+---+-----------+------------+
| c | theTel    | theDate    |
+---+-----------+------------+
| 2 | 2107710   | 2012-08-03 |
| 2 | 290117984 | 2011-08-08 |
| 2 | 400018758 | 2009-10-02 |
+---+-----------+------------+
3 rows in set
I need to set value to 1 for duplicate records.
I tried this:
Code:
UPDATE `doTable` query1
JOIN (
       SELECT
	COUNT(*) c,
	theTel,
	theDate
FROM
	`doTable`
GROUP BY
	theTel,
	theDate
HAVING
	c > 1;
) AS query2 ON query1.theTel = query2.theTel
SET query1.dual = 1;
but it updated ALL the records that where duplicate.
E.g.:
Code:
+-----------+------------+-------+
| theTel    | theDate    | Dual  |
+-----------+------------+-------+
| 400018758 | 2009-10-02 |  1    |
| 400018758 | 2009-10-02 |  1    |
+-----------+------------+-------+
Instead I want to update only one of the duplicate records:
Code:
+-----------+------------+-------+
| theTel    | theDate    | Dual  |
+-----------+------------+-------+
| 400018758 | 2009-10-02 |  0    |
| 400018758 | 2009-10-02 |  1    |
+-----------+------------+-------+
Any help be would appreciated, thank you.