UPDATE on GROUPED table

Databases
1

Hello.

I have the following mysql code which will return the duplicated rows (by post_name):

SELECT post_name, post_title, 
 COUNT(post_name) AS c
FROM wp_posts
GROUP BY post_name
HAVING ( COUNT(post_name) > 1 )

I’d like to SET each of those post_name column’s to ‘’ (to empty)

I tried:

UPDATE wp_posts
SET post_name = ''
FROM wp_posts
GROUP BY post_name
HAVING ( COUNT(post_name) > 1 )

But it will give me an error…

How should I preform UPDATE on those sql calls for each duplicated record?
I could not find any relevant/helpful information on internet.

Thanks for help!

2

I have done a little bit of searching and came to WHERE EXISTS condition:

UPDATE wp_posts SET post_name = ''
WHERE EXISTS
(SELECT dup.post_name FROM wp_posts AS dup
GROUP BY dup.post_name HAVING count(id) > 1
WHERE dup.post_name = wp_posts.post_name)

But this will give me error on the last line…

3

Heres another version, but it gives me error: #1093 - You can’t specify target table ‘wp_posts’ for update in FROM clause

UPDATE wp_posts
SET post_name = ''
WHERE id IN(SELECT id FROM wp_posts
INNER JOIN (SELECT post_name FROM wp_posts
GROUP BY post_name HAVING count(id) > 1) dup ON wp_posts.post_name = dup.post_name)

This code will return all ids of duplicate post-name rows:

SELECT id FROM wp_posts
INNER JOIN (SELECT post_name FROM wp_posts
GROUP BY post_name HAVING count(id) > 1) dup ON wp_posts.post_name = dup.post_name
4

well, see, the thing is… you can’t specify target table ‘wp_posts’ for update in FROM clause :slight_smile:

run your dupes into a temp table

this is an acceptable strategy whenever dealing with dupes, because after you fix your data, you’re going to do something so that the dupes never happen again, right?

:wink:

5

So theres no other way around this?

6

why should it matter? you’re only going to do this once, right?