How to insert where not exists-CodePudding

To prevent data duplication, I tried to use the 'where not exists' syntax.

I can't set that column to a unique value. This only necessary in certain situations. Duplicate data is sometimes possible.

Here's my table test (id: Auto increment)

id	val1	val2
1	1	1
2	2	2
3	3	3

I tried this:

insert into test (val1, val2) 
select 4, 4 from test
where not exists 
(select * from test 
where val1 = 4 and val2 = 4 
limit 1);

Here's what I actually got:

id	val1	val2
1	1	1
2	2	2
3	3	3
4	4	4
5	4	4
6	4	4

Here's the result I was expecting:

id	val1	val2
1	1	1
2	2	2
3	3	3
4	4	4

And when I run the same query after that, the insertion didn't happen as I intended. However, if there is no duplicate data, only one row should be entered.

What should I do?

CodePudding user response：

What you wanted to do here is to not select from any table (or use dual if you do want a FROM clause:

INSERT INTO test (val1, val2) 
SELECT 4, 4
WHERE NOT EXISTS (
    SELECT 1
    FROM test
    WHERE val1 = 4 AND val2 = 4
);

Your current query will insert the tuple (4, 4) for every record in the test table, which isn't the behavior you want.