How to INSERT INTO SELECT by considering IF EXISTS rules in MySQL?

Question

In MySQL its possible to insert data retrieved from another table, e.g.:

INSERT INTO
    `table2`
    (
        `table2_id`,
        `foo`
    )
SELECT
    `id`,
    `foo`
FROM
    `table1`
WHERE
    ...
;

On the other side it's also possible to define an ON DUPLICATE strategy:

• INSERT IGNORE statement
• ON DUPLICATE KEY UPDATE clause
• REPLACE statement

Now. How to combine both and insert data from another table, but handle duplications for every row? For REPLACE it's clear -- it works by just replacing INSERT by REPLACE in the query above. But how to apply the IGNORE and UPDATE behaviour by INSERTing data SELECTed from another table?

Answer 1

Such a combination is indeed possible:

basic query

INSERT INTO
    `table2`
    (
        `table2_id`,
        `foo`
    )
SELECT
    `id`,
    `foo`
FROM
    `table1`
WHERE
    ...

REPLACE

REPLACE INTO
    `table2`
    (
        `table2_id`,
        `foo`
    )
SELECT
    `id`,
    `foo`
FROM
    `table1`
WHERE
    ...

ON DUPLICATE KEY UPDATE

INSERT INTO
    `table2`
    (
        `table2_id`,
        `foo`
    )
SELECT
    `id`,
    `foo`
FROM
    `table1`
WHERE
    ...
ON DUPLICATE KEY UPDATE
    `foo` = `table1`.`foo`
;

INSERT IGNORE

INSERT IGNORE
    `table2`
    (
        `table2_id`,
        `foo`
    )
SELECT
    `id`,
    `foo`
FROM
    `table1`
WHERE
    ...
;