php - 两表多次删除

Question

表APP

counter | Item100001  | Ballpen  100002  | Pencil  100003  | Bond Paper  100004  | Marker

Table Purchase_Order

counter | Status100001  | Yes  100001  | Yes  100001  | Yes  100004  | No

I want to delete all of counter 100001 in app and purchase_order tables. How can I do that?

And this is my code

app.php

<a href="app.php?de='.$row["counter"].'">

if(isset($_GET['de']))
{
$del_id=$_GET['de'];

sql= ...

}

Answer 1

一般的方法查询看起来像这样

DELETE a, p
  FROM 
(
  SELECT 100001 counter
) q LEFT JOIN app a 
    ON q.counter = a.counter LEFT JOIN purchase_order p
    ON q.counter = p.counter;

它会删除两个表中的行，即使其中一个表没有包含 counter = 100001 的行

这是 SQLFiddle 演示