I have 2 tables t and t1. In this case, t1 is a copy of t. I want to delete rows from t1 based on criteria on the t table and a relationship between t ad t1 (in this case the id column). In the results below, I would think that the delete should have deleted row 1 {1 5 me) and not row 3 (1 5 they) when I run this statement
delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me';
Any ideas on why row 2 is deleted?
You're deleting from t1 but your name = 'me' condition is on t. t has two rows with id 1 and name 'me'. The query then deletes from t1 rows with id 1.
Did you mean t1.name = 'me' ?
Same results on Your MySQL connection id is 38495 to server version: 4.0.10-gamma-nt Windows 2000 or Your MySQL connection id is 221 to server version: 4.0.13-Max Linux 8.0
This is the contents of t & t1 to start mysql> select * from t1; +------+------+------+ | id | no | name | +------+------+------+ | 1 | 5 | me | | 2 | 7 | you | | 1 | 5 | they | | 2 | 5 | me | | 3 | 7 | you | | 3 | 7 | they | | 3 | 5 | we | +------+------+------+ 7 rows in set (0.00 sec)
This is the delete statement
mysql> delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me'; Query OK, 2 rows affected (0.00 sec)
This is the result mysql> select * from t1; +------+------+------+ | id | no | name | +------+------+------+ | 2 | 7 | you | | 2 | 5 | me | | 3 | 7 | you | | 3 | 7 | they | | 3 | 5 | we | +------+------+------+ 5 rows in set (0.01 sec)
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
