Insert WHERE NOT EXISTS

Question

I have a table that I regularly update by inserting a csv file which is dumped from another source. This file contains exisiting data in my table as well as new data. What I currently do is delete the contents of the table that relate to the data dump and re-import using the following php script:

if(isset($_POST['submit']))
{
$file = $_FILES['file']['tmp_name'];

$handle = fopen($file,"r");

while(($fileop = fgetcsv($handle,1000, ",")) !== false)
{
    $player_id = $fileop[1];
    $fixture_id = $fileop[3];
    $opponent = $fileop[6];
    $home_away = $fileop[7];
    $team_id = $fileop[8];
    $season_id = $fileop[9];
    $fixture_date = $fileop[11];
    $runs = $fileop[15];
    $sixes = $fileop[17];
    $fours = $fileop[16];
    $how_out = $fileop[25];
    $wk_catches = $fileop[26];
    $fielder_catches = $fileop[27];
    $fielder_stumpings = $fileop[28];
    $_fielder_runouts = $fileop[29];
    $wickets = $fileop[30];
    $bowling_runs = $fileop[31];
    $balls_bowled = $fileop[32];
    $maidens = $fileop[33];
    $bowled = $fileop[37];
    $caught = $fileop[38];
    $lbw = $fileop[39];
    $stumped = $fileop[40];
    $position = $fileop[47];

    $sql= mysql_query("INSERT INTO stats_game (player_id, fixture_id, opponent, home_away, team_id, season_id, fixture_date, runs, fours, sixes, how_out, wk_catches, fielder_catches, fielder_stumpings, _fielder_runouts, wickets, bowling_runs, balls_bowled, maidens, bowled, caught, lbw, stumped, position) VALUES ('$player_id', '$fixture_id', '$opponent', '$home_away', '$team_id', '$season_id', '$fixture_date', '$runs', '$fours', '$sixes', '$how_out', '$wk_catches', '$fielder_catches', '$fielder_stumpings', '$_fielder_runouts', '$wickets', '$bowling_runs', '$balls_bowled', '$maidens', '$bowled', '$caught', '$lbw', '$stumped', '$position') WHERE NOT EXISTS (SELECT * from stats_game)");
}
if($sql){
    echo 'data uploaded successfully';}else{
        die(mysql_error());
}
}

?>

What I really want to do is to just insert the data that does not currently exsit in the table. I have tried to do this via adding:

WHERE NOT EXISTS (SELECT * from stats_table)

but this is throwing a syntax error...

Could you please suggest a method of doing this.

So you want to check the exact duplicate that matches by all columns? — zerkms, May 11 '13 at 12:08
Yes, because the data dump has no primary key and all of the columns are capable of having duplicate values — user1873931, May 11 '13 at 12:11

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

You can use the

Insert ...On Duplicate key update

Please refer to the following link it may help you

INSERT ... ON DUPLICATE KEY UPDATE Syntax

Or You can see this post

How to insert if not exists

Hope that would help ! :)

edited May 23 '17 at 12:13

Community

1
1

answered May 11 '13 at 12:15

Muhannad A.Alhariri

2,728
3
25
45

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

May be you want this:

WHERE field NOT IN (SELECT field FROM stats_table) ;

Also you may take a look at MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result

edited May 23 '17 at 10:25

Community

1
1

answered May 11 '13 at 12:10

vikingmaster

7,637
1
20
38

Insert WHERE NOT EXISTS

2 Answers2