Nick Nick - 3 months ago 22
MySQL Question

MySQL - merge two tables - with condition

I want to merge 2 tables looking like this, into a single table, and for duplicate key rows to add the oldest

DateAdded
value in the merged table.

(Key1,Key2)
are the
PRIMARY KEY
.

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| Key1 | int(10) | NO | PRI | | |
| Key2 | int(10) | NO | PRI | | |
| DateAdded | DATETIME | NO | | | |
+-----------+-----------+------+-----+-------------------+-----------------------------+

Answer

Feel free to change the table names to your specifics:

INSERT INTO table_merged (Key1, Key2, dateAdded)
SELECT Key1, Key2, MIN(dateAdded) dateAdded
FROM (
    SELECT Key1, Key2, dateAdded
    FROM table1
    UNION ALL
    SELECT Key1, Key2, dateAdded
    FROM table2
  ) a
GROUP BY Key1, Key2  

UPDATE: Alternatively, this should also work:

INSERT INTO table_merged (Key1, Key2, dateAdded)
SELECT Key1, Key2, dateAdded
FROM table1
ON DUPLICATE KEY UPDATE
  dateAdded = CASE WHEN VALUES(dateAdded) < dateAdded THEN VALUES(dateAdded) ELSE dateAdded END;

INSERT INTO table_merged (Key1, Key2, dateAdded)
SELECT Key1, Key2, dateAdded
FROM table2
ON DUPLICATE KEY UPDATE
  dateAdded = CASE WHEN VALUES(dateAdded) < dateAdded THEN VALUES(dateAdded) ELSE dateAdded END;