DELETE from table1, table2 WHERE id=12345

[Jason C]

I have two tables that I join together for SELECT queries. One lists 3 columns (id, type, and expiration) where 'id' and 'type' are UNIQUE, then the other lists about 30 columns of data, where 'id' is a PRIMARY. The 'id' for the two tables will always match up.

My question is, what is the proper syntax to DELETE from both tables at once, based on the 'id' column? Currently, I run 2 queries, but it would be better if I could do it in one:

DELETE FROM table1 WHERE id=12345;
DELETE FROM table2 WHERE id=12345;

[Denis McMahon]

http://dev.mysql.com/doc/refman/5.1/en/delete.html gives the following
examples:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

I guess you need to adapt these to your requirement, assuming this is the
sql you are looking for.

Rgds

Denis McMahon

[Brian Cryer]

"Jason C" <jwca...@gmail.com> wrote in message
news:22879852.161.1328142923702.JavaMail.geo-discussion-forums@vbxy22...

Whilst I can see the elegance of doing it all in one query (see Denis'
post), it wouldn't surprise me if running your current two separate deletes
is slightly faster - if your tables are very large then it might be worth
benchmarking it, but with proper indexes then for most applications I'm sure
both would be fast enough. What I would say is that if you delete via two
separate deletes then its worth wrapping it up inside a transaction, to
avoid the risk of ending up with the record only having been deleted from
one table and not the other.
--
Brian Cryer
http://www.cryer.co.uk/brian

[onedbguru]

On Feb 2, 4:44 am, "Brian Cryer" <not.h...@localhost.invalid> wrote:
> "Jason C" <jwcarl...@gmail.com> wrote in message

how did you define the child table? If you used the "FOREIGN KEY
and the ON DELETE CASCADE" on the Child table you only need to delete
from the parent. If you delete from the child first, you will still
need to delete from the parent. If you did not use the "ON DELETE
CASCADE" clause you could cause orphans in the child table that will
need to be cleaned up. If you want to see the proper "join" syntax for
deleting from multiple tables, I an not quite certain why you didn't
just google it..

http://dev.mysql.com/doc/refman/5.0/en/delete.html

see this example using FOREIGN KEY and ON DELETE clause on the child
table.

mysql>
mysql>
mysql> CREATE TABLE parent (id INT NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE child (id INT, parent_id INT,
-> INDEX par_ind (parent_id),
-> FOREIGN KEY (parent_id) REFERENCES
parent(id)
-> ON DELETE CASCADE
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> insert into parent values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

*** Test to make sure the constraint is functioning *****

mysql> insert into child values (1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint f
ails (`mytest/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
(`parent_id`) REFERE
NCES `parent` (`id`) ON DELETE CASCADE)
mysql> insert into child values (1,3);
Query OK, 1 row affected (0.00 sec)


mysql> select * from parent;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

mysql> insert into child values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from parent;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select a.id,b.id,b.parent_id from parent a left outer join
child b on b.p
arent_id=a.id;
+----+------+-----------+
| id | id | parent_id |
+----+------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+----+------+-----------+
3 rows in set (0.02 sec)

mysql> delete from parent where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select a.id,b.id,b.parent_id from parent a left outer join
child b on b.p
arent_id=a.id;
+----+------+-----------+
| id | id | parent_id |
+----+------+-----------+
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+----+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from parent;
+----+
| id |
+----+
| 2 |
| 3 |
+----+
2 rows in set (0.00 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 2 | 2 |
| 3 | 3 |
+------+--------+
1 row in set (0.00 sec)

mysql>