Hi, I'm just testing the new multi-table delete in MySQL-4.0.2 and I wonder if I'm doing something wrong with this query :
mysql> DELETE FROM searchmainhardwarefr2,searchjoinhardwarefr2 USING searchmainhardwarefr2 as a,searchjoinhardwarefr2 as b,forumconthardwarefr2 as c WHERE b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'; Query OK, 21 rows affected (0.34 sec) (I want to delete rows from searchmainhardwarefr2 and searchjoinhardwarefr2 which match the join) As you can see, MySQL display 21 row affected, but I tried the following : mysql> SELECT COUNT(*) FROM searchmainhardwarefr2 as a,searchjoinhardwarefr2 as b,forumconthardwarefr2 as c WHERE b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'; +----------+ | COUNT(*) | +----------+ | 1744709 | +----------+ 1 row in set (1 min 19.34 sec) That's not 21 rows :) The delete seems to erase nothing since the select was issued AFTER the delete. Moreover, the returned number of rows seems to be random : mysql> DELETE FROM searchmainhardwarefr2,searchjoinhardwarefr2 USING searchmainhardwarefr2 as a,searchjoinhardwarefr2 as b,forumconthardwarefr2 as c WHERE b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'; Query OK, 21 rows affected (0.34 sec) mysql> DELETE FROM searchmainhardwarefr2,searchjoinhardwarefr2 USING searchmainhardwarefr2 as a,searchjoinhardwarefr2 as b,forumconthardwarefr2 as c WHERE b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'; Query OK, 24 rows affected (0.38 sec) mysql> DELETE FROM searchmainhardwarefr2,searchjoinhardwarefr2 USING searchmainhardwarefr2 as a,searchjoinhardwarefr2 as b,forumconthardwarefr2 as c WHERE b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'; Query OK, 28 rows affected (0.31 sec) mysql> DELETE FROM searchmainhardwarefr2,searchjoinhardwarefr2 USING searchmainhardwarefr2 as a,searchjoinhardwarefr2 as b,forumconthardwarefr2 as c WHERE b.numreponse=a.numreponse AND b.topic=c.numeropost AND c.date<'2002-01-01'; Query OK, 19 rows affected (0.44 sec) Tell me if I'm doing something wrong, and if not I will try to send a testcase ASAP at bugs mailing list. Regards, Jocelyn Fournier --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
