Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Delete data from database

AnBad's profile image AnBad posted 12 months 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 12 months 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
AnBad's profile image AnBad posted 12 months 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.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 12 months 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)
AnBad's profile image AnBad posted 12 months ago Permalink

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

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.