SQL-Update under a external condition-CodePudding

Is it possible to limit the execution of an SQL CRUD-statement with a condition that is completely unrelated to the table it is working on?

E.g. execute the UPDATE only if there is a special date.

UPDATE tabfoo SET name="santa" where id="123"   // only if day = 31.12.

I like to have this inside a single statement. I know how to do it inside a script. Me platform would be MySql or SqLite.

CodePudding user response：

For SQLite you can use the function strftime()

WHERE id = '123'
  AND strftime('%d.%m', CURRENT_DATE) = '31.12'

and for MySql the function DATE_FORMAT():

WHERE id = '123'
  AND DATE_FORMAT(CURRENT_DATE, '%d.%m') = '31.12'

CodePudding user response：

You can try UPDATE tabfoo SET name="santa" where id="123" and day = 31.12.

otherways you may try a sql stored procedure. if

CodePudding user response：

In MySQL the problem can be solved next way:

UPDATE tabfoo 
SET name="santa" WHERE
    id="123" AND  MONTH(CURDATE()) = 12 AND DAY(CURDATE()) = 31;  // only if day = 31.12.

UPDATE tabfoo 
    SET name="santa" WHERE
        id="123" AND DATE_FORMAT(CURDATE(), '%d.%m') = '31.12';

For SQLite you can find alternative function