Ad Code

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

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 |
+----+---------+---------------+---------+

 
 
 

Post a Comment

0 Comments