I have table t1 and t2 with same column names in both tables, column a and b. I want to enter data of column a and b from t2 into t1 at the same time check weather any data pre exist in t1. If data exist already in a and b column then don't enter that data. The main challenge in my case is , I want to use combination of (a and b) column data to check for pre existing values. Thanks for your precious time in advance.
INSERT INTO t1 (a,b) SELECT a, b FROM t2 EXCEPT SELECT a, b FROM t1
The EXCEPT operator compares the result sets of two queries and eliminates those rows in the first result set that match in the second result set. It uses an exact match to identify and eliminate rows.
Add Unique key to the combination of fields a and b of table t1
ALTER TABLE `t1` ADD UNIQUE INDEX `un_indx` (`a` ASC, `b` ASC);
then insert ignore the data from table t2 to table t1
insert ignore t1(a,b) select a,b from t2;
It will ignore data which violates unique key condition and will insert the rest.