Re: MySQL Multi Table Delete

[Paul DuBois]

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/