At 11:39 -0500 7/16/03, Gordon wrote:
>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/
