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!
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...
With a unique key defined in TB2 over (col1, col2), you could use INSERT IGNORE ... SELECT:
INSERT IGNORE INTO TB2 (col1, col2, col3) SELECT col1, col2, col3 FROM TB1;
I would do it like this:
INSERT INTO TB2 (col1, col2, col3) SELECT col1, col2, col3 FROM TB1 ON DUPLICATE KEY UPDATE col3=col3;
with the unique key that eggyal told about, because from what I remember INSER IGNORE ignores all inserts that fail which may be unwanted for col3.