Increment a column in table based on another table
For MySQL only.

If the id field in the table1 is primary key or has unique constraint, the solution can consist of 2 queries:

INSERT IGNORE INTO table1 (name, id, overdue_days)
SELECT name, id, 0
FROM table2;

UPDATE table1
LEFT JOIN table2 ON table1.id=table2.id
SET table1.overdue_days = CASE WHEN table2.id IS NULL 
                               THEN 0 
                               ELSE table1.overdue_days+1 
                          END;



If record present in table1 only, it is not affected by Q1 and overdue_days is set to 0 by Q2.
If record present in both tables, it is not affected by Q1 and overdue_days is incremented by Q2.
If record present in table2 only, it is inserted by Q1 with overdue_days = 0, and then is incremented by Q2.


For PostgreSQL.


  PostgreSQl is not my favorite DBMS, some mistakes possible


INSERT INTO table1 (name, id, overdue_days)
SELECT name, id, 1
FROM table2
ON CONFLICT DO UPDATE SET overdue_days = overdue_days+1;

UPDATE table1
SET overdue_days = 0
WHERE NOT EXISTS (SELECT 1
                  FROM table2
                  WHERE table1.id = table2.id);



If record present in table1 only, it is not affected by Q1 and overdue_days is set to 0 by Q2.
If record present in both tables, it's overdue_days is incremented by Q1 and is not affected by Q2.
If record present in table2 only, it is inserted into table1 by Q1 with overdue_days = 1, which is not affected by Q2.