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
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
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
Method-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 |
+----+---------+---------------+---------+
0 Comments