#StackBounty: #mysql #phpmyadmin MySQL Insert into post_meta with select and join – what am I doing wrong?

Bounty: 500

So, I’m trying to run a SQL operation:

INSERT INTO wp4t_postmeta (post_id, meta_key, meta_value)
SELECT a.post_id, '_attached_image', b.ID
FROM wp4t_postmeta a 
INNER JOIN wp4t_posts b ON a.meta_value WHERE a.meta_key="_content_field_42" 
= b.guid

Essentially, I need the wp4t_postmeta table updated with a post ID, meta key (attached_image), and meta_value.

I need the value to come from wp4t_posts ID, when the meta_key from wp4t_postmeta _content_field_42 matches the guid of wp4t_posts.

I was at first running out of size in the tmp dir everytime I tried to run it, so I added a limit to test it:

INSERT INTO wp4t_postmeta (post_id, meta_key, meta_value)
SELECT a.post_id, '_attached_image', b.ID
FROM wp4t_postmeta a 
INNER JOIN wp4t_posts b ON a.meta_value WHERE a.meta_key="_content_field_42" 
= b.guid LIMIT 10;

Now I am getting the error “#1062 – Duplicate entry ‘0’ for key ‘PRIMARY'”

I understand this is the meta_id auto increment table from wp4t_postmeta, but I thought if I didn’t include it, it would auto auto-increment?

What am I doing wrong here?

Also, is there a better way to write this query?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.