Inserting rows with WHERE condition

Question

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.

Answer 1

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)

Answer 2

Query 1

Show 1 if the latest ID row in the database has scan date of today otherwise show 0.

Can be written more simply (and more efficiently):

SELECT CASE WHEN scan.date = CURDATE() THEN 1 ELSE 0 END AS datecheck
FROM scan
ORDER BY scan.id DESC
LIMIT 1 ;

and (if we like obfuscation):

SELECT (scan.date = CURDATE()) AS datecheck
FROM scan
ORDER BY scan.id DESC
LIMIT 1 ;

Query 2

If the result of the previous query of 0, then insert a new row with ID + 1 and the current date.

INSERT INTO scan 
    (id, date) 
SELECT latest.id + 1, CURDATE() 
FROM 
  ( SELECT scan.id, scan.date
    FROM scan
    ORDER BY scan.id DESC
    LIMIT 1 
  ) AS latest
WHERE latest.date <> CURDATE() ;

---

Note: All the above queries check only the row with the latest (highest) id value. Your wording is ambiguous. There may be cases where a row with lower id has the curent date as date value. It's not clear what you want to happen in that case!

If you do want to check the highest date of the table, then you probably need to replace the ORDER BY scan.id DESC with ORDER BY scan.date DESC, which will check the highest date (and whether it's the current date).

If you want to check all the table for any rows with current date, then you need to replace that with WHERE scan.date <= CURDATE() ORDER BY scan.date DESC, which will check the whole table for any rows with current date.