MySQL Multi Table Delete

[Gordon]

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?

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)

mysql> show create table t;
+-------+---------------------------------------------------------------
------------------------------------------------------------+
| Table | Create Table
|
+-------+---------------------------------------------------------------
------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int(11) default NULL,
`no` int(11) default NULL,
`name` char(20) default NULL
) TYPE=MyISAM |
+-------+---------------------------------------------------------------
------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------
-------------------------------------------------------------+
| Table | Create Table
|
+-------+---------------------------------------------------------------
-------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) default NULL,
`no` int(11) default NULL,
`name` char(20) default NULL
) TYPE=MyISAM |
+-------+---------------------------------------------------------------
-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 select * from t;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

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)

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)

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)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw