Kakadela Horosho Kakadela Horosho - 3 months ago 11
MySQL Question

copy values from one table to another with WHERE

I have two tables and I want to add info from one table to another, but tables have equal rows.

INSERT INTO wp_posts (
id,
post_author,
post_content,
post_title,
post_name,
post_type
)
SELECT id,
3,
post_content,
post_title,
post_name,
post_type
FROM wp_posts2
WHERE post_type='Vacancy'


So I get error

[Err] 1062 - Duplicate entry '7142' for key 'PRIMARY'


How can I add only new rows?

Answer

Get only new IDs:

INSERT INTO wp_posts ( 
      id, 
      post_author, 
      post_content, 
      post_title,
      post_name,
      post_type
            ) 
SELECT  id, 
      3, 
      post_content, 
      post_title,
      post_name,
      post_type
FROM wp_posts2
WHERE  post_type='Vacancy'
and id not in (select id from wp_posts)