發表者討論內容

冷日
(冷日) 發表時間：2013/9/11 8:27

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]如何在 MySql 裡刪除選取出來的資料主鍵？（How to delete from select in MySQL?）

How to delete from select in MySQL?

This code doesn't work for MySQL 5.0, how to re-write it to make it work

DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) > 1 ))

I want to delete columns that dont have unique id. I will add that most of the time its only one id(I tried the in syntax and it doesnt work as well).

SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause:

DELETE FROM posts WHERE id IN (
    SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
)

EDIT: as shown by this answer you cannot modify the same table you selected from a subquery within the same query. However, you can either SELECT then DELETE in separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):

DELETE FROM posts WHERE id IN (
    SELECT * FROM (
        SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
    ) AS p
)

I had a table with 150 duplicate keys. I executed the above query and it said "144 rows affected", but there where still duplicate keys. So I executed the query again and it says 5 rows affected, again: 1 row affected. Then all the duplicate keys where gone. Why is this?

DELETE
  p1
  FROM posts AS p1
CROSS JOIN (
  SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)

try changing the where id = (select ... to where id IN (select

冷日：簡單來說就是『WHERE 語句應該用「IN」不是「=」』！
原文出處：sql - How to delete from select in MySQL? - Stack Overflow

回覆

主題一覽的下方

討論串

前一個主題 | 下一個主題 | 頁首 |

| |

發表新主題

DB研討會 : [轉貼]如何在 MySql 裡刪除選取出來的資料主鍵？（How to delete from select in MySQL?）