CODEWITHSUNDEEP
mysqlwordpress
Tom Dyer
Tom Dyer

Reputation: 452

MySQL update column from another column of same name

I'm having a bit of trouble with an mySQL statement that I'm sure isn't htat complex, but I'm not that well versed in mySQL so now I'm stuck. The database I'm talking of is wordpress, so hopefully a lot of people will understand the structure but I'll do my best to explain what I need and what I've tried...

My issue is this:

I have some data that I need copying from one excerpt to another... basically, I've entered a caption on a load of attachments, but I need that caption to be copied across to the post_excerpt on 'download' post_types instead. They're linked in the database by some metadata called '_download_attachment_id' So, I need to set the 'download' post_excerpt to the attachment post_excerpt where the 'download' ID equals the meta_value (attachment ID) in the postmeta table.

I realised that attachment the 'caption' is stored in the 'post_excerpt' column within wp_posts so I'm confused as to how to update one post_excerpt to another post_excerpts's value.

To try and make it easier, I created a VIEW using the this SQL

CREATE VIEW  `attachment_excerpt` AS 
SELECT a.ID aid, a.post_excerpt as excerpt, b.post_id pid
FROM rpz_posts a, rpz_postmeta b
WHERE a.ID = b.meta_value
AND b.meta_key = '_download_attachment_id'

That gives me a view that has everything I need with different names. Now, with that view setup I'm able to use the following SQL statement to grab everything I need...

SELECT rpz_posts.ID, attachment_excerpt.excerpt
FROM rpz_posts, attachment_excerpt
WHERE rpz_posts.ID = attachment_excerpt.pid 
AND rpz_posts.post_excerpt = '';

That gives me the ID of the post I need to update, and the excerpt text that I need to update it to.What I can't get working is the update... I tried this but it didn't work:

UPDATE rpz_posts SET rpz_posts.post_excerpt = attachment_excerpt.excerpt
WHERE rpz_posts.ID = attachment_excerpt.pid 
AND rpz_posts.post_excerpt = '';

The error I get is: #1054 - Unknown column 'attachment_excerpt.pid' in 'where clause'

I realised this is a long post, so I hope I've not over-complicated it. If anyone can point me in the right direction I would eternally grateful!

Upvotes: 0

Views: 186

Answers (1)

Leandro Carracedo
Leandro Carracedo

Reputation: 7345

Your update should look like:

UPDATE rpz_posts,attachment_excerpt 
SET rpz_posts.post_excerpt = attachment_excerpt.excerpt
WHERE rpz_posts.ID = attachment_excerpt.pid 
AND rpz_posts.post_excerpt = '';

This is because you have to indicate the two tables involved in order to perform the match.

MySQL docs on update.

Upvotes: 1

Related Questions