INSERT query ( Special case )

[sukatoa]

Good day,

I dont know if this would be called a special case since this will be my first time to have an idea where i would like to insert a value from the table with a condition

Is it possible?

For example:

I have a table 'tablename', and has a column 'values' and expected to put millions of unique values in that table. The constraints would be, that column will also accept NULL value and should not be set to primary key or unique(just following the specification). Therefore i must check if the new value is already exists on that table.

ALGO:
1. receive new value
2. check if the new value exists on the table
3. if not exists, add, else, dont

The following algorithm is simple and straight forward, however, when the number of rows will already on the hundreds of thousands in count, i observed the slight overhead... and when those rows will be on approximately a million or more... the system will be on its worst performance. Therefore, a question arises and that would be:

Is it possible to insert a value with a condition? like:

insert newvalue into tablename where newvalue doesnt exists in tablename

Im looking forward to your replies experts

[code green]

Various ways.
SELECT all ids from the table into an array then use INSERT... WHERE... NOT IN(array).
INSERT with NOT IN sub-query that SELECT all ids.
Use SELECT to check existence of individual id before INSERT.
Use INSERT ON DUPLICATE KEY UPDATE but simply update with same value

[nbiswas]

Solution to INSERT query ( Special case )

Suppose I have 2 tables.

TableSource

data
abc -books
new books

TableDestinatio n

data
abc -books
acc -papers

Goal is to insert those records from TableSource that are not in TableDestinatio n

Query1:

insert into TableDestinatio n
select t2.data from TableSource t2
left join TableDestinatio n t1 on t1.data = t2.data
where t1.data is null

select * from TableDestinatio n

Query2:

insert into TableDestinatio n
select data from TableSource
where data not in
(select data from TableDestinatio n)

Output:

data
abc -books
acc -papers
new books

[sukatoa]

Thank you codegreen and nbiswas for your replies :)