FeralReason FeralReason - 2 months ago 8
MySQL Question

Insert results from query into the same table

(For WordPress folks: I am creating a set of new "custom field"s called sidebar_buy_now by combining pairs of existing "custom field"s.)

I have a table:

wp_postmeta
with 4 fields:
meta_id
,
post_id
,
meta_key
,
meta_value
.

meta_id
is the primary key and should auto-increment.

I have a working SELECT query which produces the desired result for the last 3 fields:
post_id
,
meta_key
,
meta_value
:

SELECT L.`post_id`, "sidebar_buy_now" AS "meta_key",
CONCAT(L.`meta_value`, ' | ',R.`meta_value`)
AS "meta_value" FROM `wp_postmeta` L INNER JOIN `wp_postmeta` R
ON L.`post_id` = R.`post_id`
AND L.`meta_key` = 'sidebar_buy_now_url'
WHERE R.`meta_key` = 'sidebar_buy_now_title'


I want to insert the results of this query into the
wp_postmeta
table while auto-incrementing
meta_id
.

This is the INSERT query I am trying which does not work:

INSERT into `wp_postmeta` (`post_id`,`meta_key`,`meta_value`)
SELECT L.`post_id`, "sidebar_buy_now" AS "meta_key",
CONCAT(L.`meta_value`, ' | ',R.`meta_value`) AS "meta_value" FROM `wp_postmeta` L INNER JOIN `wp_postmeta` R ON L.`post_id` = R.`post_id`
AND L.`meta_key` = 'sidebar_buy_now_url' WHERE R.`meta_key` = sidebar_buy_now_title'


Getting this error:

#1136 - Column count doesn't match value count at row 1
.

Perhaps because I don't know how to specify an auto-incrementing meta-id in the SELECT... (?)
Anyone know how to fix this?

Answer
INSERT into wp_postmeta (post_id,meta_key,meta_value)
SELECT
   L.post_id
   ,"sidebar_buy_now" AS "meta_key"
   ,CONCAT(L.meta_value, ' | ',R.meta_value) AS "meta_value"
FROM
   wp_postmeta L
   INNER JOIN wp_postmeta R
   ON L.post_id = R.post_id AND L.meta_key = 'sidebar_buy_now_url'
WHERE R.meta_key = 'sidebar_buy_now_title'

Because meta_id is an auto increment column you don't actually insert anything into you. You simply omit it from the insert statement.

Comments