用PHP更新mysql数据库表

Question

My database has a lot of columns with customers info and some of them are duplicates. I need to update "sale" field of the table depending on the IP address (which is known), but only for the the latest entry with such IP address.

Here is my table:

|Sale |      IP     | Date     | 
+-----+-------------+----------+
|0    | 109.86.75.1 |2015-12-01|
|0    | 109.86.75.2 |2015-12-05|
|0    | 109.86.75.2 |2015-12-12|
|0    | 109.86.75.4 |2015-12-13|

Let's assume that I need to add changes to customer with ip = 109.86.75.2, I need to change Sale to 1 in the third row, as there are two entries with such IP, but time of the third row is the latest.

Table should look like this after update:

|Sale |      IP     | Date     | 
+-----+-------------+----------+
|0    | 109.86.75.1 |2015-12-01|
|0    | 109.86.75.2 |2015-12-05|
|1    | 109.86.75.2 |2015-12-12|
|0    | 109.86.75.4 |2015-12-13|

I use such PHP code:

<?php
$servername=...;
$username=...;
$password=...;
$dbname=...;
$ipaddress="109.86.75.2";
$conn = new mysqli($servername, $username, $password, $dbname);
$sql="UPDATE MY_DATABASE_TABLE 
SET Sale='1'
WHERE ip_address = '$ipaddress' AND //Don't know what to add here in where condition... 

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} 
else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

I tried this after AND, but it doesn't work:

date IN (SELECT max(date) FROM MY_DATABASE_TABLE)";

"Error updating record: You can't specify target table 'MY_DATABASE_TABLE' for update in FROM clause"

Your help is highly appreciated! Thanks in advance!

Answer 1

You can use an UPDATE with a LEFT JOIN:

UPDATE
  MY_DATABASE_TABLE t1 LEFT JOIN MY_DATABASE_TABLE t2
  ON t1.ip_address=t2.ip_address
     AND t1.date<t2.date
SET
  t1.Sale='1'
WHERE
  t1.ip_address = '109.86.75.2'
  AND t2.date IS NULL

t2.date is NULL when the join does not succeed: the row is the only row with that ip_address, or is the one with the maximum date.

Please see a fiddle here.