CKre CKre - 19 days ago 8
SQL Question

INSERT INTO based on WHERE clause of second table

I have two tables

table1
+--------+-----+------+
| DATE | VAR| VAL|
+--------+-----+------+
|20160101|VAR_1| 0.4|
|20160101|VAR_2| 0.1|
|20160102|VAR_1| 0.6|
|20160102|VAR_2| 0.1|
|20160103|VAR_1| 0.5|
|20160103|VAR_2| 0.1|
+--------+-----+------+

table 2
+--------+-----+------+
| DATE | VAR| VAL|
+--------+-----+------+
|20160102|VAR_1| 0.6|
|20160102|VAR_2| 0.1|
|20160103|VAR_1| 0.5|
|20160103|VAR_2| 0.1|
|20160104|VAR_1| 0.3|
|20160104|VAR_2| 0.2|
|20160105|VAR_1| 0.2|
|20160105|VAR_2| 0.6|
|20160106|VAR_1| 0.3|
|20160106|VAR_2| 0.4|
+--------+-----+------+


What I want to do is add all the new entries of the table2 into table1 based on the date. My idea was like this

INSERT INTO table1 SELECT * FROM table2 WHERE date > (SELECT MAX(date) from table1);


but I get a syntax error.
With a NOT IN it works...

INSERT INTO table1 SELECT * FROM table2 WHERE table2.date NOT IN(SELECT DISTINCT date from table1);


Any help is highly appreciated!

Answer

Here is a way try this , I used date1 instead of date

select t2.* from table2 t2, (SELECT MAX(date1) date1 FROM table1 ) t1 where t2.date1 > t1.date1;