mysql - 使用 select 语句中的 ids 插入 wp_postmeta 表

Question

我有以下查询正在运行:

    SELECT  ID 
    FROM `wp_posts` as ps 
    INNER JOIN `wp_term_relationships` as tr 
    ON ps.ID = tr.object_id 
    WHERE ps.post_date < '2011-06-07' /*Get posts before June 6th 2011*/ 
    AND ps.post_title 
    LIKE '%MY SEARCH STRING %' /*A String im looking for in post titles*/ 
    AND tr.term_taxonomy_id = '548'/*My Category ID*/;

我需要查询为每个结果 ID 插入特定数据。理论上:

 INSERT IGNORE INTO wp_postmeta (post_id,meta_key, meta_value)
 VALUES (IDs from selection above,_thumbnail_id,11059)

这将设置 meta_key至_thumbnail_id和meta_value至11059上面特定选择的所有帖子。

同时跳过将任何值插入 wp_postmeta meta_id的自动递增列通过使用wp_postmeta (post_id ..)而不是wp_postmeta (meta_id,post_id ..)

Answer 1

INSERT IGNORE INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID, '_thumbnail_id', 11059
FROM `wp_posts` as ps 
INNER JOIN `wp_term_relationships` as tr ON ps.ID = tr.object_id 
WHERE ps.post_date < '2011-06-07'
AND ps.post_title LIKE '%MY SEARCH STRING %'
AND tr.term_taxonomy_id = '548'