Hello dear community,
I came across a problem that I can’t find a solution to.
There are incorrect values (null) in my HA database that make my graphs look ugly.
I would like to delete these values. Specifically, it’s about a sensor value called “sensor.jan_weight”. Unfortunately, my scale occasionally transmits a zero value in HA.
I would like to delete these values.
To display the values, I executed the following SQL query:
SELECT states.*
FROM states LEFT JOIN states_meta ON states.metadata_id=states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.jan_weight' AND state < 50
With this query I get the entries with the zero value and all values under 50Kg. These values should be deleted. For this I wrote the following SQL script. The value “metadata_id” = 9922 corresponds to the sensor “sensor.jan_weight”:
DELETE
FROM states
WHERE metadata_id = 9922 and state < 50
This gives me the following error message:
SQL-Fehler (1451): Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)) */
As far as I understand, the problem is related to linked tables. The information I’ve found about it on Google so far hasn’t helped me. Either I don’t understand the solution or the solution doesn’t fit my requirement of only deleting individual values. Is there another way to delete individual sensor data from HA (ideally via script)?