比较2个MySQL表并将新行移动到第二个表

Question

This is my current scenario:

I have two tables (TB1 & TB2) that are exactly the same exept for one column. I have a CRON that copies everything from a remote SQL database and refreshes everything on TB1 dropping the current rows and inserting the new ones, so TB1 is my source table.

On TB2 I have the same exact structure as TB1 but the last row changes from true to false, the default is false. See the sample tables below:

TB1
+---------------+---------------+---------------+---------------+
| id            | col1          | col2          |col3           |
+---------------+---------------+---------------+---------------+
| 1             | value1        | val-A         |false          |
| 2             | value2        | val-B         |false          |
| 3             | value1        | val-C         |false          |
| 4             | value3        | val-D         |false          |
| 5             | value4        | val-E         |false          |
+---------------+---------------+---------------+---------------+



TB2
+---------------+---------------+---------------+---------------+
| id            | col1          | col2          |col3           |
+---------------+---------------+---------------+---------------+
| 1             | value1        | val-A         |true           |
| 2             | value2        | val-B         |false          |
| 3             | value1        | val-C         |true           |
| 4             | value3        | val-D         |false          |
+---------------+---------------+---------------+---------------+

As you can see the TB1 has an extra row id=5. The combination of both col1 and col2 is unique and that's what I'm trying to detect, so any new combination of both TB1.col1 AND TB1.col2 should be copied over to TB2 while keeping TB2.col3.

I tried the following and it doesn't really work in my case because it only shows all different values not the combined difference:

SELECT TB1.col1, TB1.col2 FROM (
SELECT TB1.col1, TB1.col2 FROM TB1
UNION ALL
SELECT TB2.col1, TB2.col2 FROM TB2
) TB1
GROUP BY TB1.col1
HAVING count(*) = 1
ORDER BY TB1.col1

I know I'm missing the INSERT part but any help on both or at least the SELECT will be really appreciated.

Thanx!

Answer 1

Don't think Minus is available in my sql but an outer join should work

http://sqlfiddle.com/#!2/e73e4/1/0

Insert into tb2 (
Select tb1.ID, tb1.Col1, tb1.Col2, tb1.Col3 from tb1 
LEFT JOIN tb2 on Tb1.col1=tb2.col1 and Tb1.col2=tb2.col2
Where tb2.col1 is null);

But I'm likely missing something you were after...