MySQL 多表關聯更新及删除

目錄：

《MySQL中的兩種臨時表》

《MySQL 多表關聯更新及删除》

《mysql查詢優化之三：查詢優化器提示（hint）》

一、      多表關聯更新

問題描述：現有tdb_goods表（含有具體資訊）和tdb_goods_cates表（沒有具體資訊），需要查詢tdb_goods表的所有記錄，并且按"類别"分組，且将分組結果寫入到tdb_goods_cates資料表。然後通過tdb_goods_cates資料表來更新tdb_goods表

² 查詢tdb_goods表的所有記錄，并且按"類别"分組

  SELECT goods_cate FROM tdb_goods GROUP BYgoods_cate;

²  将分組結果寫入到tdb_goods_cates資料表

  INSERT tdb_goods_cates (cate_name) SELECTgoods_cate FROM tdb_goods GROUP BY goods_cate;

²  通過tdb_goods_cates資料表來更新tdb_goods表

  UPDATE tdb_goods INNER JOIN tdb_goods_catesON goods_cate = cate_name

  SET goods_cate = cate_id ;

二、      多表關聯删除

²  從MySQL資料表t1中把那些id值在資料表t2裡有比對的記錄全删除掉

DELETE t1 FROM t1，t2 WHERE t1.id=t2.id 或DELETE FROM t1 USING t1，t2 WHERE t1.id=t2.id

²  從MySQL資料表t1裡在資料表t2裡沒有比對的記錄查找出來并删除掉

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.idWHERE t2.id IS NULL 或

DELETE FROM t1，USING t1 LEFT JOIN T2 ONt1.id=t2.id WHERE t2.id IS NULL

²  從兩個表中找出相同記錄的資料并把兩個表中的資料都删除掉

DELETE t1，t2 from t1 LEFT JOIN t2 ONt1.id=t2.id WHERE t1.id=25（注意此處的delete t1，t2 from 中的t1，t2不能是别名）

如：delete t1，t2 from table_name as t1left join table2_name as t2 on t1.id=t2.id where table_name.id=25 在資料裡面執行是錯誤的（MySQL 版本不小于5.0在5.0中是可以的）

上述語句改寫成

delete table_name，table2_name fromtable_name as t1 left join table2_name as t2 on t1.id=t2.id wheretable_name.id=25 在資料裡面執行是錯誤的（MySQL 版本小于5.0在5.0中是可以的）

²  兩張表關聯删除：

DELETE a,b FROM table1 a INNER JOIN  table2 b ON a.id = b.aid WHERE a.id = '1'或DELETE a,b FROM table1a,table2 b WHERE a.id = b.aid AND a.id = '1'

²  三張表删除：

DELETE a,b,c FROM table1 a INNER JOIN  table2 b ON a.id = b.aid

INNER JOIN table3 c ON a.id = c.aid WHERE a.id = '1'

不過這樣有一個問題，就是如果a表裡資料，而b表或者c表裡沒資料，那麼整個删除就失敗，即删除0條資料

如果你的主表一定有資料，而關聯的表有可能有資料也有可能沒資料的話，我們可以通過左連接配接删除的方式，把兩張表都删除。無論關聯的表有沒有資料，主表都可以删除成功

DELETE a ,b ,c FROM table1 a LEFT JOIN table2 b ONa.id = b.aid

LEFT JOIN table3 c ON a.id = c.aid WHERE a.id = 1

Unknown table 'a' in MULTI DELETE的解決辦法

在臨時表中的關聯删除出現錯誤“Unknown table 'a' in MULTI DELETE的解決辦法”

在存儲過程中，調用：

delete a from db1.tb1 a, db2.tb2 b where a.col1 = b.col1 and a.col2 = b.col2;

出現題目中的錯誤，​​原因如下​​：

在mysql中多表聯合删除時，表别名隻能在sql中表關聯部分聲明。我們應該避免不是表關聯部分聲明别名，因為這産生歧義的sql,進而産生不是期望的結果，例如在錯誤的表中删除行，舉個例子來說：

DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

對于多個表的删除，表的清單中提到的表别名的引用，使用預設資料庫，除非已經明确地指定了一個資料庫。例如，如果預設資料庫是db1,下面的語句将不會工作，因為不确定的别名a2被認為已經有了db1:

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2

WHERE a1.id=a2.id;

除了預設資料庫外，為了正确地比對表的别名,我們必須明确地指定正确的資料庫的名字，

DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2

根據上述理論，我的sql應該這樣寫：

delete db1.a from db1.tb1 a, db2.tb2 b where a.col1 = b.col1 and a.col2 = b.col2;

還有需要注意的是:

1.如果你為一個表聲明了别名，當你指向這個表的時候，就必須使用這個别名，例如:

-- 正确的寫法: 

DELETE t1 FROM test AS t1, test2 WHERE ... 

-- 錯誤的寫法:

DELETE test FROM test AS t1, test2 WHERE ...

2.在多個表聯合删除時，不能使用order by 或limit,而單個表的删除時就沒有這個限制。

3.目前，我們還不能在删除表的時候，在子查詢中select from相同的表。