+----+------+----+ | id | c1 | c2 | +----+------+----+ | 1 | NULL | 1 | | 2 | a | 1 | | 3 | NULL | 2 | | 4 | b | 2 | | 5 | NULL | 3 | | 6 | c | 3 | | 7 | NULL | 4 | +----+------+----+
I would like to delete all rows that c1 = NULL *and* have a duplicate c2. So, in this case I want to eliminate rows 1,3, and 5.
So my question is two-fold: 1) What's a good command way to do this?
2) I tried an UPDATE/DELETE combo:
UPDATE t1 LEFT JOIN t1 as t2 ON (t1.c2 = t2.c2 AND t2.c1 IS NOT NULL) SET t1.c2 = NULL;
DELETE FROM t1 WHERE c1=NULL and c2 =NULL;
This would only replace and delete one record at a time (so I would need to run it 3 times). Why is it only updating one row? Why not all matches or no matches?
Thanks, Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
