Removing Duplicates in SQL Database Provided
I need to remove duplicates from a table that looks like this:
id post_author post_title
-----------------------------------------------------------------------------
21319 1 Youngstown State University
20535 1 Yo San University of Traditional Chinese Medicine
30268 29 Yo San University of Traditional Chinese Medicine
29747 29 Yeshiva University
21964 1 Yale University
29247 29 Yale University
29497 29 Xavier University of Louisiana
21916 1 Xavier University
29862 29 Xavier University
29860 29 Wright State University-Main Campus
20915 1 Wright State University-Lake Campus
21562 1 World Mission University
30267 29 World Mission University
Basically, if there are two records with the same post_title
, I need to delete one with post_author = 1
, but if post_title
unique, the record should be left as is.
How do I do this with a SQL query?
EDIT:
I tried the query suggested by Mureinik. The request looks like this:
DELETE t FROM wp_posts AS t
WHERE t.post_author = 1 AND
EXISTS (SELECT * FROM wp_posts s
WHERE t.post_title = s.post_title
AND s.post_authot != 1)
But I got the error:
[Err] 1093 - You can't specify target table 't' for update in FROM clause
What am I doing wrong?
+3
source to share
3 answers
You can set a condition on post_author = 1
where there is more than one post_title
with a temporary table:
CREATE TEMPORARY TABLE t_del AS SELECT post_title
FROM t
GROUP BY post_title
HAVING count(post_title)>1;
DELETE FROM t
WHERE post_author = 1
AND post_title IN (select post_title FROM t_del) ;
+1
source to share