CODEWITHSUNDEEP
phpmysqlsql
Raza Ahmed
Raza Ahmed

Reputation: 2751

mysql insert if not exist without unique key

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.

Upvotes: 1

Views: 586

Answers (2)

user3490589
user3490589

Reputation: 23

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.

Upvotes: 1

Arjan
Arjan

Reputation: 9874

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.

Upvotes: 2

Related Questions