Question 183695 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to prevent events from inserting data that already exists in sql

so i want to create an event, which inserts values into Daily_Profit based on values from transaction.

so I have used this syntax to get a unique value and hope there will be no duplication

ALTER TABLE Daily_Profit ADD UNIQUE unique (date, profit);

but the problem is, null values from table B are always added. So it’s like this

date    profit
NULL    10
NULL    10
NULL    10
20      50
20      30
30      12

and here my event syntax

delimiter //
create event profit
on schedule every 1 second
do begin
insert ignore into Daily_Profit
(select date, sum(total_price) from transaction group by date);
end //
delimiter ;

So, is there a solution so that the null value will not be duplicated?

Tags:

2

Answers


  1. 0

    You can add null check to insert logic in the event

    delimiter //
create event profit
on schedule every 1 second
do begin
    insert ignore into daily_profit (date, profit)
    select t.date, coalesce(sum(t.total_price), 0)
    from transaction t
    left join daily_profit dp on t.date = dp.date
    where dp.date is null 
    group by t.date;
end //
delimiter ;
    Login or Signup to reply.
  2. 0

    If you want to exclude NULL values from being inserted into the Daily_Profit table, you can modify your event’s query to filter out rows where the date is NULL. Here’s an updated version of your event syntax:

    DELIMITER //
CREATE EVENT profit
ON SCHEDULE EVERY 1 SECOND
DO
BEGIN
    INSERT IGNORE INTO Daily_Profit
    SELECT date, SUM(total_price)
    FROM transaction
    WHERE date IS NOT NULL
    GROUP BY date;
END //
DELIMITER ;

    By adding the WHERE date IS NOT NULL condition to the query, you ensure that only rows with a non-NULL date value will be considered for insertion into the Daily_Profit table. This way, the NULL values won’t be duplicated in the result.

    Please note that the uniqueness constraint you defined on the Daily_Profit table (ALTER TABLE Daily_Profit ADD UNIQUE unique (date, profit);) won’t prevent NULL values from being inserted since NULL values are considered distinct from each other. If you want to exclude NULL values from being inserted into the profit column as well, you may need to modify the table schema and add a NOT NULL constraint to the profit column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search