SQL IF exist date by day do increment update else insert data

Question

How can I express the below statement as a SQL query ?

IF EXISTS (SELECT * FROM expense_history 
           WHERE user_id = 40 
             AND DATE_FORMAT(expense_history.created_date , '%Y-%m-%d') = '2018-06-02' 
             AND camp_id='80') 
    UPDATE expense_history  
    SET clicks = clicks + 1, 
        amount = amount + 1 
    WHERE user_id = 40 
      AND DATE_FORMAT(expense_history.created_date, '%Y-%m-%d') = '2018-06-02' 
      AND camp_id = '80'
ELSE
   INSERT INTO expense_history (camp_id, created_date, amount, user_id) 
   VALUES ('80', '2018-06-02 12:12:12', '1', '40')
END IF;

I just want to do increment clicks and amount if is set by day, else I want to add new row.

Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — Hasan Gholamali, Jun 02 '18 at 07:26

Barbaros Özhan · Answer 1 · 2018-06-02T13:43:17.250

I presumed your database is mysql, because of DATE_FORMAT() function(and edited your question as to be).

So, by using such a mechanism below, you can do what you want,

provided that a COMPOSITE PRIMARY KEY for camp_id, amount, user_id columns :

SET @camp_id = 80,
    @amount  =  1,
    @user_id = 40,
    @created_date = sysdate();

INSERT INTO expense_history(camp_id,created_date,amount,user_id,clicks)
VALUES(@camp_id,@created_date,@amount,@user_id,ifnull(clicks,1))
ON DUPLICATE KEY UPDATE 
    amount = @amount + 1,
    clicks = ifnull(clicks,0)+1;

SQL Fiddle Demo

score 0 · Answer 2 · answered Jun 02 '18 at 10:36

This is very tricky in MySQL. You are storing a datetime but you want the date part to be unique.

Starting in MySQL 5.7.?, you can use computed columns for the unique constraint. Here is an example:

create table expense_history (
  user_id int,
  camp_id int,
  amount int default 0,
  clicks int default 1,
  . . .
  created_datetime datetime,  -- note I changed the name
  created_date date generated always as (date(created_datetime)),
  unique (user_id, camp_id, created_datetime)
);

You can then do the work as:

INSERT INTO expense_history (camp_id, created_datetime, amount, user_id) 
    VALUES (80, '2018-06-02 12:12:12', 1, 40)
    ON DUPLICATE KEY UPDATE
        amount = COALESCE(amount + 1, 1),
        clicks = COALESCE(clicks + 1, 1);

Earlier versions of MySQL don't support generated columns. Nor do they support functions on unique. But you can use a trick on a prefix index on a varchar to do what you want:

create table expense_history (
  user_id int,
  camp_id int,
  amount int default 0,
  clicks int default 1,
  . . .
  created_datetime varchar(19), 
  unique (created_datetime(10))
);

This has the same effect.

Another alternative is to store the date and the time in separate columns.

SQL IF exist date by day do increment update else insert data

2 Answers2