473,570 Members | 2,888 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL Multi Table Delete

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

Jul 19 '05 #1
0 6446

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2391
by: el_roachmeister | last post by:
I have been using flat files for a while but thought I should learn mysql. There are two things I dont like about mysql compared to using flatfiles. They are: 1) When creating a table why do I need to define the type of data and length so strictly, i,e int , varchar etc? Can I make a flexible table with data of any type and length? 2)...
5
3992
by: red85 | last post by:
hello i have mysql 4.1 with win2000 SP3, i know that it is only an alpha and i don't know if someone else has already posted this problem: when i execute this sql UPDATE tableX SET fieldX=valueX WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=valueY AND filedZ=valueZ);
0
1756
by: Vic | last post by:
Hi all, When I test the Delete multi table function in MySQL, DELETE table_name ...] FROM table-references I accidentally delete all data in one table. All data in that table are gone when I try to select them out in Control Center. But when I go into the /mysql/data/mydatabase/, I see a MYD, MYI, frm for that table. And it seems...
0
3934
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest version of this document can be found at: http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
2
497
by: TomyB | last post by:
Here's on for you, any help would be greatly appreaciated as it is sending me mad... I have three tables tbl_photo, tbl_photo_gallery and tbl_gallery. they are linked through primary and and secondary keys as follows: tbl_photo.photo_id = tbl_photo_gallery.photo_id tbl_gallery.gallery_id = tbl_photo_gallery.gallery_id yeat when i try...
3
8707
by: saracen44 | last post by:
Hi I have MyISAM tables When I'm deleting parent I want to delete children in the same time. How can I do It? What are possibilities? Thanks
7
9770
by: Jon Maz | last post by:
Hi, I have a MySql problem I hope someone can help me with. I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: CREATE TABLE `articles_categories` (
0
6625
by: Bucker | last post by:
Could someone view the following that I copied from phpMyAdmin and tell me from the statistics if are server is running OK? Does it look like we will have problems as more people hit our server? I'm not a db admin, so any help would be grateful. Regards,
6
38477
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get through this without much trouble. Programming knowledge is not required. Index What is SQL? Why MySQL? Installing MySQL. Using the MySQL...
0
7633
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7944
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8149
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6323
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3681
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2131
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.