How to compare 2 tables with dates and check which date is missing and insert the date using Mysql?-CodePudding

How to compare 2 tables with dates and check which date is missing and insert the date using Mysql ??

table 1 : appweb_ind_exeindicadores - COLUMN: dataindicador

table 2 : calendteste - COLUMN: dataindicador2

IMAGE:

table 01

IMAGE:

table 02

CodePudding user response：

You can use something like the below to get the list of dates in one table but not the other, and then write an INSERT around this to add it in.

-- This will give you the dates in table 1 but not in table 2
SELECT DISTINCT table1.date
FROM table1
WHERE table1.date NOT IN (SELECT DISTINCT table2.date2 FROM table2)

CodePudding user response：

This should work

INSERT INTO calendteste (dataindicador2)
SELECT distinct dataindicador as dataindicador2
FROM appweb_ind_exeindicadores
WHERE dataindicador not in ( SELECT distinct dataindicador2 
                             FROM calendteste ) ;

CodePudding user response：

If you want unique dates in calendteste, I would suggest that you let the database do the verification. So, create a unique constraint on the column:

alter table calendteste add constraint unq_calendteste_dataindicador2
    unique (dataindicador2);

Then, you can insert the rows and use on duplicate key update to handle the duplicates:

INSERT INTO calendteste (dataindicador2)
    SELECT distinct dataindicador
    FROM appweb_ind_exeindicadores
    ON DUPLICATE KEY UPDATE dataindicador2 = VALUES(dataindicador2);

The actual logic is a no-op -- no update happens but the duplicate key error is ignored. This is the safe way to ensure that the column does not have duplicates.