Delete data from database

[expired user #12545]'s profile image [expired user #12545] posted 5 years ago in Running SQL scripts Permalink

Hi, can someone help me doing the following task:

Delete all rows with an timestamp older than 30 days at every device except the following:

'mqtt_klima_1.OG_Bad', 'mqtt_klima_1.OG_Schlaf', 'mqtt_klima_Aussen', 'mqtt_klima_DG_Dachboden', 'mqtt_klima_DG_Schornstein', 'mqtt_klima_EG_Garage', 'mqtt_klima_KG_Getraenke', 'mqtt_klima_KG_Werkstatt'

I tried this, but it wont work:

DELETE FROM history WHERE DEVICE NOT IN (
'mqtt_klima_1.OG_Bad',
'mqtt_klima_1.OG_Schlaf',
'mqtt_klima_Aussen',
'mqtt_klima_DG_Dachboden',
'mqtt_klima_DG_Schornstein',
'mqtt_klima_EG_Garage',
'mqtt_klima_KG_Getraenke',
'mqtt_klima_KG_Werkstatt') 
AND 
where timestamp < DATEADD(MONTH, -1, GETDATE())
ansgar's profile image ansgar posted 5 years ago Permalink

Are you on MySQL or MariaDB? In any case, you have two times the "WHERE" keyword, which must only be there one time:

DELETE FROM xyz WHERE x=1 AND y=2 AND z=3
[expired user #12545]'s profile image [expired user #12545] posted 5 years ago Permalink

I am in MariaDB, using it on an Raspberry Pi for my FHEM Server. I have no idea about SQL and unfortunally no time to get into it. So I asked for help. Thank you.

ansgar's profile image ansgar posted 5 years ago Permalink

Ok then my guess is this could be right: (I just don't know the writing of your columns device and timestamp)

DELETE FROM history
WHERE
    device NOT IN (
        'mqtt_klima_1.OG_Bad',
        'mqtt_klima_1.OG_Schlaf',
        'mqtt_klima_Aussen',
        'mqtt_klima_DG_Dachboden',
        'mqtt_klima_DG_Schornstein',
        'mqtt_klima_EG_Garage',
        'mqtt_klima_KG_Getraenke',
        'mqtt_klima_KG_Werkstatt') 
    AND timestamp < DATE_SUB(NOW(), INTERVAL 1 MONTH)
[expired user #12545]'s profile image [expired user #12545] posted 5 years ago Permalink

Thank you very much!! All the best to you!!

Please login to leave a reply, or register at first.