MySQL query to update all "duplicate" rows except the latest one

Question

SO...

I have a table (let's call it Data Location) like...

Data Location ID    Data ID    Location Type    Location URL   Status    Date
1                   1          Foo              foo/3          Valid     10-21-2014
2                   1          Bar              bar/1          Valid     10-21-2014
3                   1          Foo              foo/2          Valid     03-20-2013
4                   1          Foo              foo/1          Valid     12-01-2010

...and so on. This table has many different Data IDs (I didn't show them for simplicity). The idea is that there should only be 1 Valid entry for a given Data ID and Location Type and as you can see above, all of Foo locations for Data ID 1 are all valid (foo/1, foo/2, foo/3).

Can someone please help me construct a query to update all of the duplicate records' (same Data ID and Location Type) Status column to Invalid EXCEPT that latest entry. I have a query that can identify the rows that fit the duplicate criteria, but I am unsure of how to combine group by with max (or not max?) and update. It seems like partition by may be the way, but I am a bit rusty with queries, so I appreciate any help. So for the data above, I would expect the result to be...

Data Location ID    Data ID    Location Type    Location URL   Status    Date
1                   1          Foo              foo/3          Valid     10-21-2014
2                   1          Bar              bar/1          Valid     10-21-2014
3                   1          Foo              foo/2          Invalid   03-20-2013
4                   1          Foo              foo/1          Invalid   12-01-2010

...thanks in advance!

Answer 1

You can use one UPDATE statement:

UPDATE Data_Location u
INNER JOIN (
  SELECT `Data ID`, `Location Type`, MAX(`Date`) AS max_date
  FROM Data_Location
  GROUP BY `Data ID`, `Location Type`
) t ON u.`Data ID` = t.`Data ID` 
   AND u.`Location Type` = t.`Location Type`
SET u.Status = 'Invalid'
WHERE u.`Date` <> t.max_date

Test on SQL Fiddle

Answer 2

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Data_ID INT  NOT NULL
,Location_Type    VARCHAR(5) NOT NULL
,Status    VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,1,'Foo','Valid'),
(2,1,'Bar','Valid'),
(3,1,'Foo','Valid'),
(4,1,'Foo','Valid');     

SELECT * FROM my_table;
+----+---------+---------------+--------+
| ID | Data_ID | Location_Type | Status |
+----+---------+---------------+--------+
|  1 |       1 | Foo           | Valid  |
|  2 |       1 | Bar           | Valid  |
|  3 |       1 | Foo           | Valid  |
|  4 |       1 | Foo           | Valid  |
+----+---------+---------------+--------+

UPDATE my_table x 
  JOIN my_table y 
    ON y.data_id = x.data_id 
   AND y.location_type = x.location_type 
   AND y.id < x.id 
   SET x.status = 'Invalid';

SELECT * FROM my_table;
+----+---------+---------------+---------+
| ID | Data_ID | Location_Type | Status  |
+----+---------+---------------+---------+
|  1 |       1 | Foo           | Valid   |
|  2 |       1 | Bar           | Valid   |
|  3 |       1 | Foo           | Invalid |
|  4 |       1 | Foo           | Invalid |
+----+---------+---------------+---------+


mysql>

Answer 3

• Set all of them as invalid with a single and simple query like UPDATE table SET status = 'invalid'.
• Then perform another query where you group rows by data ID and location type and order them by ID in descending order. Set the selected rows as valid. I'm not sure if the two queries I provide below can be merged and I don't have the environment to test right now, but it would work this way:

-

$query = $yourPDO->prepare('SELECT id FROM table ORDER BY id DESC GROUP BY data_id, location_type');
$query->execute();
$results = $query->fetchAll(PDO::FETCH_OBJ);

$ids = [];

// not sure if there is an array_* function for this functionality:
foreach ($results as $row)
{
    $ids[] = $row->id;
}

$yourPDO->prepare('UPDATE table SET status = "valid" WHERE id IN (' . implode(', ', $ids) . ')');