how insert registers of today from another table mysql

user_19866317 0
Viewed 44

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 I keep getting this 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 SQL also causes an error:

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 yet another attempt:

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

This 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;
mysql sql database
2 Answers

You have defined grown column as not null so you cannot put there NULL.

My query 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;
user_169458030 
INSERT INTO results (Data, total)
    SELECT CURRENT_DATE(), COUNT(*)
    FROM records
    WHERE DATE(Data) = CURRENT_DATE();
user_193084210
Related