how insert registers of today from another table mysql-CodePudding

I'm trying to count the records in my "records" table and insert in results table but I just want to count today's records below you will see some alternatives that I tried (I'M USING MYSQL), but keeps me giving error

You have a syntax error in your SQL next to '' on line 2

INSERT INTO results (Data,total)
      VALUES ( now(),(SELECT COUNT(*) FROM records WHERE Data= now() );

this to

INSERT INTO results (Data,total)
      VALUES ( now(),(SELECT COUNT(record.ID) as day FROM record WHERE date(Data)=date(date_sub(now(),interval 0 day));

and then

INSERT INTO resultS (Data,total)
      VALUES ( now(),(SELECT COUNT(*)  FROM records WHERE Data >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY));

and more

INSERT INTO results (Data,Total) VALUES ( now(),(SELECT COUNT(*) FROM records WHERE DATE(Data)= CURRENT_DATE() - INTERVAL 1 DAY));

here is my sql config man

CREATE TABLE `records` (
  `ID` char(23) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Total` int(255) NOT NULL,
  `Data` date NOT NULL,
  `QrCode` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Device` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `results` (
  `id` int(11) NOT NULL,
  `total` int(11) NOT NULL,
  `Data` date DEFAULT NULL,
  `grown` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CodePudding user response：

You have defined grown column as not null so you cannot put there NULL. My querry works :

INSERT INTO results VALUES (1, (SELECT COUNT(1) FROM records WHERE Data= now()), now(),1 );

You should define default value for grown column. Same situation you have with column id. You should define sequence for column id:

id  NOT NULL AUTO_INCREMENT;

CodePudding user response：

I suggest think about uniq index on your tables.

CodePudding user response：

INSERT INTO results (Data, total)

SELECT CURRENT_DATE(), COUNT(*)

FROM records

WHERE DATE(Data) = CURRENT_DATE();