mysql insert if not exist without unique key

Question

As discussed in MySQL: Insert record if not exists in table and https://stackoverflow.com/a/5289299/2037323 i'm trying to run following query from php and $result = mysql_query($query, $con); returns true but new records are not adding to teamshalf table. Whats wrong here? http://pastebin.com/xCqWpKcb is data that i used for both tables. One thing to mention here is that i can not use unique key here. So please tell if there is solution for non unique.

INSERT teamshalf
(`yearID` , `lgID` , `teamID` , `Half` , 
`divID` , `DivWin` , `Rank` , `G` , `W` , `L`)
 SELECT DISTINCT t1.`yearID` , t1.`lgID` , t1.`teamID` ,
 t1.`Half` , t1.`divID` , t1.`DivWin` , t1.`Rank` , t1.`G` , t1.`W` , t1.`L` 
 FROM `teamshalf_TEMP` t1 LEFT JOIN `teamshalf` t2 ON t1.`yearID` = t2.`yearID`
 AND t1.`lgID` = t2.`lgID` AND t1.`teamID` = t2.`teamID`
 AND t1.`Half` = t2.`Half` AND t1.`divID` = t2.`divID`
 AND t1.`DivWin` = t2.`DivWin` AND t1.`Rank` = t2.`Rank`
 AND t1.`G` = t2.`G` AND t1.`W` = t2.`W` AND t1.`L` = t2.`L`
 WHERE t1.`yearID` IS NULL AND t1.`lgID` IS NULL AND t1.`teamID` IS NULL
 AND t1.`Half` IS NULL AND t1.`divID` IS NULL AND t1.`DivWin` IS NULL
 AND t1.`Rank` IS NULL AND t1.`G` IS NULL AND t1.`W` IS NULL AND t1.`L` IS NULL

SOLUTION As mentioned by @Arjan i was using t1.col_name is null instead of t2.col_name is null. So by modifying that i got desired result.

i changed to `INSERT INTO teamshalf` but still no new record entered to db. teamshalf_TEMP contain two different records, rest are same — Raza Ahmed, Apr 03 '14 at 20:22
For future code: You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a name that is a reserved word, or you have embedded spaces or punctuation in them, and those are terrible practices to avoid anyway. — Andy Lester, Apr 03 '14 at 20:29
why is `yearID` a VARCHAR(2048) ? will it ever be anything but 4 numbers? — , Apr 03 '14 at 20:32
@AndyLester quote is because it is dynamically generated query and may contain columns with spaces. — Raza Ahmed, Apr 03 '14 at 20:35
@Dagon VARCHAR(2048) as it is loaded from csv and i don't know what that will be.Generated dynamically. — Raza Ahmed, Apr 03 '14 at 20:35
@TheRaaaZ I don't know what problem you are trying to solve, but loading data from csv files into dynamically generated tables without indexes and without proper column types, and then using those tables in queries, does not look like the proper way to me. Sure, it'll be fast with your small set of test data, but you don't want to try this with (tens of) thousands of rows in those tables. — Arjan, Apr 03 '14 at 20:45
Oooops..! I got stuck again. Working fine for small files but now when i tested with 8mb file, it continues loading. :( @Arjan can you please provide a suggestion? i'm dynamically from csv using `LOAD DATA LOCAL INFILE` and comparing with above query — Raza Ahmed, Apr 03 '14 at 21:11
The solution is to use proper indexes. But if you only have dynamically generated tables it'll be hard to determine what a proper index would be. — Arjan, Apr 03 '14 at 21:25

score 2 · Accepted Answer · answered Apr 03 '14 at 20:25

The query is successful (otherwise mysql_query() would not return true) yet it does not insert any rows. The only explanation for that is that the SELECT part results in an empty set. That is because the LEFT JOIN returns rows that exist in t1 but not in t2, but the select is done on NULL rows in t1.

This should work:

INSERT teamshalf
(`yearID` , `lgID` , `teamID` , `Half` , 
`divID` , `DivWin` , `Rank` , `G` , `W` , `L`)
 SELECT DISTINCT t1.`yearID` , t1.`lgID` , t1.`teamID` ,
 t1.`Half` , t1.`divID` , t1.`DivWin` , t1.`Rank` , t1.`G` , t1.`W` , t1.`L` 
 FROM `teamshalf_TEMP` t1 LEFT JOIN `teamshalf` t2 ON t1.`yearID` = t2.`yearID`
 AND t1.`lgID` = t2.`lgID` AND t1.`teamID` = t2.`teamID`
 AND t1.`Half` = t2.`Half` AND t1.`divID` = t2.`divID`
 AND t1.`DivWin` = t2.`DivWin` AND t1.`Rank` = t2.`Rank`
 AND t1.`G` = t2.`G` AND t1.`W` = t2.`W` AND t1.`L` = t2.`L`
 WHERE t2.`yearID` IS NULL

Also note that mysql_query() is deprecated. And you should declare columns that must contain data as NOT NULL, so NULL will not be accepted. And your query will be slow, because you're not using indexes.

thanks @arjan I got your point and now it is working. Thanks,solved a big problem. just for details, i'm updating question with working query. — Raza Ahmed, Apr 03 '14 at 20:37

score 1 · Answer 2 · answered Apr 03 '14 at 20:26

IMHO you should create a unique key by concatenating the fields that makes a record unique. (a record is never exactly the same as another record). For example:

insert into table_a
(field1, field2, field3, field4)
select t_field1, t_field2, t_field3, t_field4
from temp_table 
where concatenate(t_field1, t_field2, t_field3, t_field4)
not in (select concatenate(field1, field2, field3, field4) from table_a)

I'm not sure what the equivelent of concatenate is in mysql but I hope you understand the explanation.

A primary key in MySQL can contain more than one column. And comparing function results is a bad idea because it cannot be indexed (at least in MySQL) — Arjan, Apr 03 '14 at 20:31

mysql insert if not exist without unique key

2 Answers2

Linked