INSERT INTO WHERE NOT IN or NOT EXIST where table origin has duplicates

user_1789378 0
Viewed 66

I am having problem when trying to insert record where i do not want duplicates. This problem is different from any solution i found in google so here it goes:

I have TABLE1 and TABLE2 and i want to insert record from TABLE1 into TABLE2 so i use this query:

INSERT INTO table1
SELECT *
FROM table2
WHERE table2.importId NOT IN 
(
    SELECT importId 
    FROM table1 
);

This works only if TABLE2 is guaranty does not contain duplicates importId. So if TABLE2 has duplicates importId and i want to insert into TABLE1 which doesn't contain importId from TABLE2. The script above will persist on inserting records thus creating duplicates importId in TABLE1.

I already tried: 

INSERT INTO table1
SELECT *
FROM table2
WHERE NOT EXIST 
(
    SELECT importId 
    FROM table1
    WHERE table1.importId <> table2.importId
);

Same results occurs! Now i am having to write another query to remove duplicates. So is there a way round this problem?

I use MYSQL in phpmyadmin BTW.

phpmyadmin mysql php
1 Answers

You will need to use the DISTINCT keyword for this, see. https://www.w3schools.com/sql/sql_distinct.asp

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

Should be what you want, but if you need to not have duplicates across only one column I think the GROUP BY keyword will work. Maybe something like this

INSERT INTO table1
SELECT *
FROM table2
WHERE table2.importId NOT IN 
(
    SELECT importId 
    FROM table1 
) GROUP BY (table2.importId);
user_25062130
Related