michaelmcgurk michaelmcgurk - 11 days ago 5
SQL Question

Increment column value if duplicate value found

I have a very simple script that imports a CSV of Postcodes and creates a row in a table with it.

I also have a column to store the

total
number of times that Postcode appears in the CSV file.

Is it possible to increment a column value if a duplicate postcode is found?

So, if there's 2 instances of w1a 1aa it would increment the value in the total column. For example:

id | postcode | total
0 | w1a 1aa | 2
1 | g74 1bc | 1
2 | ml10 6qd | 1


Many thanks for any help with this.

 

Table:

id | postcode | total


PHP:

// connect to db
$servername = "localhost";
$username = "";
$password = "";
$dbname = "postcodes";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

foreach($csv as $row) {

$postcode = $row[0];

$sql = 'INSERT INTO postcodes VALUES (NULL, "'.$postcode.'", 1)';

if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

}

Answer

MySQL allows you to phrase something like the following (can't check syntax right now, but you will find it well documented in the internet):

INSERT ... ON DUPLICATE KEY UPDATE...

This means, attempt to insert a row into a table that has a specific column(s) as unique keys and, if a row exists with the same value of key, it executes the update part.

So, if you define the column post code as a unique index, the above construct will do exactly what you are asking for.

Comments