MySQL query to update all "duplicate" rows except the last one
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
... etc. There are many different data IDs in this table (I just did not 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 Foo locations for Data ID 1 are valid (foo / 1, foo / 2, foo / 3).
Can someone please help me build a query to update all status columns of duplicate records (same data id and location type) to invalid EXCEPT of this last record. I have a query that can identify rows that match duplicate criteria, but I'm not sure how to combine a group with max (or not max?) And update. It seems like the section might be like this, but I'm a little rusty with requests, so I appreciate any help. So for the data above, I 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!
source to share
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
SQL Fiddle Testing
source to share
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>
source to share
- Set all of them as invalid with a simple and simple query for example
UPDATE table SET status = 'invalid'
. - Then run another query where you group rows by data id and location type and order them by id in descending order. Set the selected lines as valid. I'm not sure if the two queries I provide below can be combined and I don't have a testing environment right now, but it will work like this:
-
$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) . ')');
source to share