Delete false data from mysql feed

maybe @TrystanLea or @glyn.hudson knows how to crack this nut

OK. The reason I asked is that your picture shows the data value as 99.999 in feeds 8 through 12 as well as feed 7. So you’re saying that feeds 8 through 12 contain data values less than 80 as well as more than 80?

the pic show feed 7 selected… so its only showing for feed 7 not the others

but to answer… yes the other feeds have data that could be anything

the only common in all 6 feeds is the time collum

Got it.
If you know the range of the timestamps, you can specify that.

a range is only good if its all false data in that range…

Making sure we’re on the sam sheet of music…
Do you wan’t to zap ALL of feeds 8 through 12 or just parts of them?

only parts

i want to zap in feeds 8 to 12 that has the time value that we get from feed 7

Got it. If memory serves, that’ll take a “nested” select clause.

Something along the lines of:

DELETE FROM mytable1 WHERE value IN (SELECT key FROM mytable2 WHERE <filtering_condition>)

Ref:

Again, something similar to that, as opposed to that exact syntax.

I think this is what you’re looking for:

The only difference being your timestamp will come from the same table.

i googled and asked a bit arround and yes the answer is close enough

SELECT * FROM feed_7 f7 LEFT JOIN feed_8 f8 ON f8.time = f7.time LEFT JOIN feed_9 f9 ON f9.time = f7.time LEFT JOIN feed_10 f10 ON f10.time = f7.time LEFT JOIN feed_11 f11 ON f11.time = f7.time LEFT JOIN feed_12 f12 ON f12.time = f7.time WHERE f7.data > 80

thumbsup

and to do the actual delete

DELETE f7, f8, f9, f10, f11, f12 
FROM feed_7 f7 
LEFT JOIN feed_8 f8 ON f8.time = f7.time
LEFT JOIN feed_9 f9 ON f9.time = f7.time
LEFT JOIN feed_10 f10 ON f10.time = f7.time
LEFT JOIN feed_11 f11 ON f11.time = f7.time
LEFT JOIN feed_12 f12 ON f12.time = f7.time
WHERE f7.data > 80

Good deal. Thanks for sharing that.

and i found wrong values in feed_8 too, ie over 80 degrees

so i rewrote it to

DELETE f7, f8, f9, f10, f11, f12 
FROM feed_8 f8 
LEFT JOIN feed_7 f7 ON f7.time = f8.time
LEFT JOIN feed_9 f9 ON f9.time = f8.time
LEFT JOIN feed_10 f10 ON f10.time = f8.time
LEFT JOIN feed_11 f11 ON f11.time = f8.time
LEFT JOIN feed_12 f12 ON f12.time = f8.time
WHERE f8.data > 80

does that look about right?

Have you ran the first one yet?

yep, worked fine

Then I’d say the second one ought to be OK too.

and i have equal amount of records in all 6 feeds, so must be right :smiley:

1 Like

highfive

1 Like