vivienne vivienne - 7 months ago 17
SQL Question

SQL query for insert into with update on duplicate key

I have two tables

debitTable
and
creditTable
.

debitTable
has the following records:

+----+-------+
| id | debit |
+----+-------+
| a | 10000 |
| b | 35000 |
+----+-------+


and
creditTable
has these records:

+----+--------+
| id | credit |
+----+--------+
| b | 5000 |
+----+--------+


How about the SQL Server query to produce these results:

+----+----------------+--------------+
| id | debit | credit | debit-credit |
+----+----------------+--------------+
| a | 10000 | 0 | 10000 |
| b | 35000 | 5000 | 30000 |
+----+-------+--------+--------------+

Answer

You want to use a join. However, it is important to aggregate before joining:

select coalesce(d.id, c.id) as id, coalesce(credit, 0) as credit,
       (coalesce(debit, 0) - coalesce(credit, 0)) as DebitMinusCredit
from (select id, sum(debit) as debit
      from debit
      group by id
     ) d full outer join
     (select id, sum(credit) as credit
      from debit
      group by id
     ) c
     on d.id = c.id;

This uses full outer join to ensure that all records from both tables are included, even if an id is not in one of the tables. The aggregation before joining is to avoid Cartesian products when there are multiple rows for a single id in both tables.

Comments