PostgreSQL – How to Increment a Column Based on Another Table

MySQLpostgresql

I have 2 table in my database.

Table #1

----------------------------
| name | id | overdue_days |
----------------------------

Table #2

-------------
| name | id | 
-------------

This is to maintain a fine collection database where overdue_days in table #1 corresponds to how many days the fine submission by a particular id pending is done.

Table #2 receives every day the names of defaulters for that day.

My intention is to compare table #1 and table #2 and

  1. Increment the overdue days for ids which are present in table #2 and are already present in table #1.
  2. If a particular id is present in table #1 but not in table #2 , then set its overdue days to 0.
  3. If an id is present in table #2 but not in table #1, insert a row for it with overdue days as value 1.

My desired result would be updated table #1 with the above conditions.

I can think of one way to use three separate join queries and cover each of the cases. 

update T1
set overdue_days = overdue_days + 1
from table#1 T1
inner join table#2 T2
on T1.id = T2.id;

Is there an optimised way to do the above?

Best Answer

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.
Related Question