0

I am trying to use the following code to update my post_title column by removing the first word in it. 

UPDATE wp_posts 
SET post_title = (
SELECT SUBSTRING(post_title, LOCATE(' ', post_title)+1) as post_title
FROM wp_posts
WHERE post_type = 'post');

When using just this code 

SELECT SUBSTRING(post_title, LOCATE(' ', post_title)+1) as post_title
FROM wp_posts
WHERE post_type = 'post';

It doesn't give out any errors, and it shows a list of all the rows with everything updated, but when I go back to the table, nothing is actually updated. I'm assuming that's because this just selects all of the rows or something. That's why I tried the update code, which is not working and giving the following error 

You can't specify target table 'wp_posts' for update in FROM clause

Can someone tell me what's wrong with my code

Maaz
  • 4,193
  • 6
  • 32
  • 50
  • possible duplicate: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Ian McMahon Feb 24 '13 at 09:46
  • 1
    The error message says it all. You can't update and select in/from the same table in one SQL-call. – Sirko Feb 24 '13 at 09:46
  • I realize as much, but I'm not well versed with mySQL hence why I ask for some help. – Maaz Feb 24 '13 at 09:53

2 Answers2

1

the following should work.

UPDATE wp_posts SET post_title = SUBSTRING(post_title, LOCATE(' ', post_title)+1) WHERE post_type = 'post';
Eldarni
  • 425
  • 1
  • 3
  • 9
  • Doesn't seem to work. Comes up with a syntax error. I think it was missing a bracket right in front of "SUBSTRING" so I added it, and still comes up with a syntax error highlighting `WHERE post_type = 'post')` – Maaz Feb 24 '13 at 09:51
  • oh removing the end bracket seems to have fixed the issue :) – Maaz Feb 24 '13 at 09:54
0
UPDATE  wp_posts a
        INNER JOIN wp_posts b
            ON a.post_type = b.post_type
SET     a.post_title = SUBSTRING(b.post_title, LOCATE(' ', b.post_title) + 1)
WHERE   b.post_type = 'post'
John Woo
  • 258,903
  • 69
  • 498
  • 492