Update mysql database table with PHP -

my database has lot of columns customers info , of them duplicates. need update "sale" field of table depending on ip address (which known), the latest entry such ip address.

here 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 need add changes customer ip = 109.86.75.2, need change sale 1 in third row, there 2 entries such ip, time of third row latest.

table should 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' ip_address = '$ipaddress' , //don't know add here in condition...   if ($conn->query($sql) === true) { echo "record updated successfully"; }  else { echo "error updating record: " . $conn->error; }  $conn->close(); ?> 

i tried after and, doesn't work:

date in (select max(date) my_database_table)"; 

"error updating record: can't specify target table 'my_database_table' update in clause"

your highly appreciated! in advance!

you can use update left join:

update   my_database_table t1 left join my_database_table t2   on t1.ip_address=t2.ip_address      , t1.date<t2.date set   t1.sale='1'   t1.ip_address = '109.86.75.2'   , t2.date null 

t2.date null when join not succeed: row row ip_address, or 1 maximum date.

please see fiddle here.