The correct query is:
UPDATE
lp_plates_backup AS t
INNER JOIN (
SELECT
plate_uid, brand, model, date_validated
FROM
lp_pictures_backup as parent
WHERE
brand <> '' AND
date_validated = (
SELECT MAX(date_validated)
FROM lp_pictures_backup as t2
WHERE t2.plate_uid = parent.plate_uid
GROUP BY
plate_uid)
) AS m ON
m.plate_uid = t.uid
SET
t.brand = m.brand,
t.model = m.model
WHERE
t.brand <> m.brand
OR
t.model <> m.model;
Just some little explanations. You need INNER JOIN because you must update row of lp_plates_backup only if plate_uid exists into lp_plates_backup. ORDER BY is useless because you are selecting all rows, order is not important.
You need the max date_validated of rows grouped by plate_uid, select MAX(date_validate) must individuate only a row for plate_uid through the date_validated field. So you need to add group by to select max(..).
The query select plate_uid returns a row for plate_uid so you don't need to aggregate here. The condition of single row is already builded into select max subquery.
I hope you did not get confused by me :-)
Updated
The previous works well if date_validate is the pair (plate_uid, date_validated) is unique.
If you have this kind of data:
| plate_uid | brand | model | date_validated |
| 1 | Fiat | Panda | 2014-10-11 10:03:20 |
| 1 | BMW | 7-Series | 2014-10-11 10:03:20 | <- changed data
| 1 | BMW | 7-Series | 2014-07-28 19:14:02 |
| 1 | Mercedes | S-Class | 2014-06-12 08:54:57 |
| a | Tesla | Model S | 2014-12-17 11:00:00 |
| a | BMW | 3-Series | 2014-11-07 14:34:11 |
The following query returns the first two rows for plate_uid 1.
SELECT plate_uid, brand, model, date_validated
FROM lp_pictures_backup as parent
WHERE
brand <> '' AND
date_validated = (
SELECT MAX(date_validated)
FROM lp_pictures_backup as t2
WHERE t2.plate_uid = parent.plate_uid
GROUP BY plate_uid)
The update will choice the values from the first or the second row. I think of the following alternatives:
- using some other
lp_pictures_backup fields to choise between rows with the same date_validated.
- enforcing a unique constraint on table. I.e.
alter table lp_pictures_backup add unique index (plate_id, date_validated). Rejecting invalid data.
- detecting valid
lp_pictures_backup pair of (plate_uid, date_validated). Updating lp_plates_backup only with valid pairs, review invalid pairs and correct them.
Maybe there are more alternatives. I prefer enforcing contraint on data so to have better data. I expand 3th alternative. Just create a view to define what a valid pair (plate_uid, date_validated) is:
CREATE VIEW lp_pictures_backup_valid as
SELECT plate_uid, date_validated
FROM lp_pictures_backup as parent
WHERE date_validated = (
select max(date_validated)
from lp_pictures_backup t2
where t2.plate_uid = parent.plate_uid GROUP BY plate_uid
)
group by plate_uid, date_validated
having count(*) = 1; <-- you can change this to make an invalid row
A valid pair (plate_uid, date_validated) is a pair with the max date_validated only if there are a unique date_validated value.
I rewrite the update statements to consider only valid pair:
UPDATE
lp_plates_backup AS t
INNER JOIN (
-- modification start
SELECT p.plate_uid, p.brand, p.model, p.date_validated
FROM lp_pictures_backup as p
INNER JOIN lp_pictures_backup_valid valid
ON p.plate_uid = valid.plate_uid and
p.date_validated = valid.date_validated
WHERE p.brand <> '')
-- modification end
)
AS m ON m.plate_uid = t.uid
SET
t.brand = m.brand,
t.model = m.model
WHERE
t.brand <> m.brand
OR
t.model <> m.model;
Hope this make sense.
Update: 2014-03-20
In the first case:
Blockquote
1. using some other lp_pictures_backup fields to choise between rows with the same date_validated.
I have assumed your date is like this:
|id| plate_uid | brand | model | date_validated |
|4 | 1 | Fiat | Panda | 2014-10-11 10:03:20 |
|3 | 1 | BMW | 7-Series | 2014-10-11 10:03:20 |
|2 | 1 | BMW | 7-Series | 2014-07-28 19:14:02 |
|1 | 1 | Mercedes | S-Class | 2014-06-12 08:54:57 |
|2 | a | Tesla | Model S | 2014-12-17 11:00:00 |
|1 | a | BMW | 3-Series | 2014-11-07 14:34:11 |
You can try this:
UPDATE
lp_plates_backup AS t
INNER JOIN (
SELECT t1.plate_uid, t1.brand, t1.model, t1.date_validated
FROM lp_pictures_backup as t1,
(SELECT t2.plate_uid, MAX(id) as id, MAX(date_validated) as dv
FROM lp_pictures_backup as t2
GROUP BY t2.plate_uid) as t3
WHERE t1.brand <> '' AND
t1.plate_uid = t3.plate_uid AND
t1.date_validated = t3.dv AND
t1.id = t3.id
) AS m ON
m.plate_uid = t.uid
SET
t.brand = m.brand,
t.model = m.model
WHERE
t.brand <> m.brand
OR
t.model <> m.model;
The fields used to choise the row to be updated are extracted by this part:
...
(SELECT t2.plate_uid, MAX(id) as id, MAX(date_validated) as dv
FROM lp_pictures_backup as t2
GROUP BY t2.plate_uid) as t3
...
So it assumes a correlation between date_validated and id: at increasing dates corresponds to increasing id.
Hope it help.
It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages...
something like this, which uses the language setting to control isdate and convert.
set language english;
update test_dates
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;
set language british;
update test_dates
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;
If the table is too large to update in big chunks, then you should look at wrapping that in a loop with a row limit and a transaction around each update and repeat until zero rows have been updated.
Best Answer
?