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.
Best Answer
You must convert your values list to rowset.
For example:
This query will give NULL for the row for which there is no
city
value inarea
table. If you need some custom string literal then use COALESCE() function, i.e. notSELECT city, ...
butSELECT COALESCE(city, 'Empty') city, ...
.Depends on MySQL version you may use another methods: ROW() function, JSON_TABLE() function, etc.