Delete all row based on condition in MySQL

Question

I want to delete rows based on condition. Let me explain in detail,

table1

 -----------------
| t1id | t1detail |
|---------------  |
|  1   |  iii     |
|  2   |  jjj     |
|  3   |  iik     |
|  4   |  jjk     |
 -----------------

table2

 ------------------------
| t2id | t1id | t1detail |
|----------------------- |
|  1   |   1  | iii      |
|  2   |   1  | jja      |
|  3   |   3  | iib      |
|  4   |   1  | jjc      |
|  5   |   2  | iid      |
|  6   |   3  | jje      |
|  7   |   4  | iif      |
|  8   |   2  | jjg      |
|  9   |   3  | iih      |
|  10  |   4  | jj3      |
 ------------------------

Now I need to delete table1 id(t1id) at the same time in table2 all id assigned from tablet1 should be deleted.

for example, Suppose I need to delete t1id = 1 mean, it should delete from table1 and table2

in table1

 1   |  iii

in table2

1   |   1  | iii
2   |   1  | jja
4   |   1  | jjc

should be deleted. Kindly advice me to do in mysql

I tried with

    $query = "DELETE FROM table1 INNER JOIN table2 ON table1.t1id = table2.t1id  WHERE  table1.t1id = {$id};
    $result = mysql_query($query, $connection);

but not successfull

One way would be to use foreign key constraints between the two tables, with "cascade on delete". dev.mysql.com/doc/refman/5.0/en/create-table-foreign-keys.html — mable
– mable, Commented Jun 25, 2014 at 15:15
@mable : Could you please share the exact query for my problem ? — Vinoth Kumar
– Vinoth Kumar, Commented Jun 25, 2014 at 16:58

Brian Driscoll · Accepted Answer · 2014-06-25 21:26:14Z

1

I'm going to assume that you don't have foreign keys setup (maybe using MyISAM storage?). If that's the case you will need to specify both tables to delete from in your query.

DELETE table1, table2
FROM table1
JOIN table2
ON table1.t1id = table2.t1id
WHERE table1.t1id = {$id}

If you had foreign keys setup you could use cascading delete, which would solve your issue as well.

edited Jun 25, 2014 at 21:26

answered Jun 25, 2014 at 15:14

Brian Driscoll

19.7k3 gold badges51 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

solick Over a year ago

Agree with Brian. How about a stored Procedure for this?

Brian Driscoll Over a year ago

@mable I don't think you're right on this one. Both the DELETE reference page and the JOIN reference page indicated that you can use JOINS in a DELETE statement.

mable Over a year ago

@BrianDriscoll that's what I get for skimming ;)

Collectives™ on Stack Overflow

Delete all row based on condition in MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related