Given the two tables:
create table table_A (col1 int, col2 int);
insert into table_A values(10,10);
insert into table_A values(15,15);
insert into table_A values(35,35);
insert into table_A values(45,45);
create table table_B (col1 int, col2 int);
insert into table_B values(10,10);
insert into table_B values(2000,2000);
insert into table_B values(35,35);
insert into table_B values(6000,6000);
I want to copy contents of each row from table_B to table_A except if the lines are duplicated. Correct output after applying a LEFT JOIN is:
select * from table_A;
+------+------+
| col1 | col2 |
+------+------+
| 10 | 10 |
| 15 | 15 |
| 35 | 35 |
| 45 | 45 |
| 2000 | 2000 |
| 6000 | 6000 |
+------+------+
select * from table_B;
+------+------+
| col1 | col2 |
+------+------+
| 10 | 10 |
| 2000 | 2000 |
| 35 | 35 |
| 6000 | 6000 |
+------+------+
The query I used is:
INSERT IGNORE INTO test_leftjoin.table_A (
SELECT DISTINCT test_leftjoin.table_B.*
from test_leftjoin.table_B
LEFT JOIN test_leftjoin.table_A
ON (
test_leftjoin.table_B.col1 = test_leftjoin.table_A.col1 and
test_leftjoin.table_B.col2 = test_leftjoin.table_A.col2
)
WHERE (
test_leftjoin.table_A.col1 IS NULL AND
test_leftjoin.table_A.col2 IS NULL
)
);
This is simple because there are only two columns in the tables, but if I have to write the same query for tables with 20-30 columns it gets very complicated and big.
Are there alternatives to using the JOIN here or simplifying the ON and WHERE matches to include all columns?
Thanks in advance.
As you are using MariaDB 10.6.11 you can use the EXCEPT clause, which was added in 10.3:
INSERT INTO table_A
SELECT * FROM table_B EXCEPT ALL SELECT * FROM table_A;
For your sample data, SELECT * FROM table_A;
returns:
col1 | col2 |
---|---|
10 | 10 |
15 | 15 |
35 | 35 |
45 | 45 |
2000 | 2000 |
6000 | 6000 |
Here's a db<>fiddle.