Search code examples
mysqldatetimeconditional-statementsinsert-update

MySQL conditional insert or update based on date

I'm trying to do something like:

IF EXISTS(SELECT * FROM weatherbyday WHERE DATE(DateTime) = CURDATE()) 
THEN update weatherbyday 
SET  
TempMin='[th0temp-dmin]', 
TempMax='[th0temp-dmax]'
WHERE DATE(DateTime) = CURDATE()
ELSE INSERT INTO weatherbyday (DateTime, TempMin, TempMax ) VALUES ('[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]', '[th0temp-dmin]','[th0temp-dmax]' )

but IF can't be used in this syntax.

There are plenty of answers to this sort of problem conditional on duplicate keys, but I want to use the date as a condition, which can't be a key.

Can anyone provide an elegant answer?

Thank you David

Solution

  • One simple answer is to use an integer created from the date as the unique key. Then you can use ON DUPLICATE KEY. This works fine for my purpose.

    INSERT INTO weatherbyday (wdID, DateTime, TempMin, TempMax,RainDay) 
    VALUES ('[YYYY][MM][DD]', '[YYYY]-[MM]-[DD] [hh]:[mm]:[ss]', '[th0temp-dmin]', '[th0temp-dmax]','[rain0total-daysum]')
  ON DUPLICATE KEY UPDATE TempMin = '[th0temp-dmin]', TempMax = '[th0temp-dmax]',RainDay='[rain0total-daysum]'

    I am using the above code to upload weather station readings using Meteobridge - a hardware/software device which connects a weatherstation to the internet. The variables are expressed in a way which the device software understands. The MySQL creates a single row per day with cumulative readings - which can be extended to more columns of course. The code will be the basis of tables for monthly and yearly records, for which Meteobridge helpfully supplies variables which contain the totals.

    The above should be useful as an example for any application where a date based record needs to be updated if it exists, or created if not.