find wrong entry in a table using mysql query

Question

I have a table 'example' which looks like this

id index   date        number
============================
1   10   2016-01-01     26
2   10   2016-01-02     0
3   10   2016-01-03     26
4   11   2016-01-01     39

I wish to find the record which was updated wrongly. In this case it is the row with id 2(as you can see the next and previous date values for 'number' column are same for the query select * from example where index=10 order by date desc);

"as you can see the next and previous date values are same" I cant — Matt, Jun 14 '16 at 13:22
select * from example where index=10 order by date desc; Here, the value 'number' is different for id 2 — Sahanaa01, Jun 14 '16 at 13:28
So for the row to be correct the number has to be the same as the number of the other rows with the same index? Otherwise I still don't see how to spot the wrong data — Philipp, Jun 14 '16 at 13:32
*Here, the value 'number' is different for id 2* so why `id = 3` is wrong again? — Alex, Jun 14 '16 at 13:33
We don't know what's wrong with your data, why it's wrong, or what it's supposed to look like. You're going to have to provide more details. — Michael McGriff, Jun 14 '16 at 13:33
@Matt select * from example where index=10 order by date desc; Here, the value 'number' is different for id 2 which is 0 and not 26. Hence it is wrong. — Sahanaa01, Jun 14 '16 at 17:30
@MichaelMcGriff id 2 is supposed to have 26 in 'number' column and not 0. — Sahanaa01, Jun 14 '16 at 17:30
@Alex yes. id 2 is wrong.. Now how do i find it out using query. Please help out — Sahanaa01, Jun 14 '16 at 17:31
*id 2 is supposed to have 26 in 'number' column and not 0* so any 0 value is wrong? or any value differ from 26 is wrong? — Alex, Jun 14 '16 at 18:01
@Alex id 2 should have been 26 and not 0. I need a query to find out that id 2 is wrong and replace 0 with the correct value 26. — Sahanaa01, Jun 14 '16 at 18:11

score 0 · Answer 1 · answered Jun 14 '16 at 20:37

0

Your requirements are rather ambiguous so this is based on my assumptions of them.

SELECT id, index, MIN(date) AS date, number
FROM example
WHERE index = 10
GROUP BY id, index, number

answered Jun 14 '16 at 20:37

Matt

14,906
27
99
149

find wrong entry in a table using mysql query

1 Answers1