Inserting rows with WHERE condition

Structure

1 table named: scan

2 columns named: id, and date

Problem

I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the condition is equal to 0.

Query 1

Show '1' if the latest ID row in the database has scan date of today otherwise show '0' when no today's date is present.

SELECT
  COUNT(scan.date) AS datecheck
FROM scan
WHERE scan.id = (SELECT
    MAX(scan.id) AS datecheck
  FROM scan
  WHERE scan.date = CURDATE())

Query 2

If there is no entry for today, meaning a results of '0' then create a new row with ID+1 and the current date.

INSERT INTO `scan`(`id`, `date`) SELECT (MAX(id)+1), CURDATE() FROM scan;

I have been trying for hours now with no luck yet, I would be grateful if you can tell me where I am going wrong and let me know what the correct query is. Thank you.

Rephrasing your query it seems like you simply want to check if there's no row with today's date. It's a bit tricky to make it an conditional Insert:

INSERT INTO scan (id, date)
SELECT (MAX(id)+1), CURDATE()
FROM scan
HAVING COUNT(CASE WHEN scan.date = CURDATE() THEN 1 end) = 0;

To make it work even with an empty table change to (COALESCE(MAX(id),0)+1)

StackExchange Database Administrators Q#134280, answer score: 5