Problem with creating a row. If there is, update it. If not, create it-CodePudding

INSERT INTO `businesses_stats` 
        (ID, Biz_id, Biz_Date) 
VALUES ((   SELECT `ID` 
            FROM businesses_stats 
            WHERE Biz_id = '1' 
            AND Biz_Date = CURRENT_DATE
        ),'1',CURRENT_DATE) 
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue 1000;

Does not work. Error #1093.

INSERT INTO `businesses_stats` (`Biz_id`, `Biz_Date`) 
        SELECT '1', CURDATE() FROM DUAL 
        WHERE NOT EXISTS (SELECT * FROM `businesses_stats` 
        WHERE `Biz_id`='1' AND `Biz_Date`= CURDATE() LIMIT 1)
        ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue 1000;

Does not work.

Question: How to do this, if there is Biz_date = CurDate and Biz_id = 1, then update. Otherwise, create a new one with these parameters. Attention! The Biz_date and Biz_id columns cannot be the same in the same row, but both rows are not unique.

CodePudding user response：

INSERT INTO `businesses_stats` (`ID`,`Biz_id`, `Biz_Date`, `Biz_Revenue`) 
SELECT (SELECT `ID` FROM businesses_stats WHERE Biz_id = '1' 
AND Biz_Date = CURRENT_DATE Limit 1) as ID, '1', CURDATE(), '1000' FROM DUAL 
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue 1000;

My solution is this. It works. Сan it be redone?

CodePudding user response：

To achieve the desired effect, simply create a unique index using the expression:

ALTER TABLE businesses_stats
ADD UNIQUE INDEX ((CASE WHEN Biz_id = '1' THEN 1 END), Biz_Date);

then use the query

INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue) 
VALUES ('1', CURRENT_DATE, @Biz_Revenue)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue   VALUES(Biz_Revenue);

or (if Biz_Revenue increasing value is a constant)

INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue) 
VALUES ('1', CURRENT_DATE, 1000)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue   1000;

DEMO