CODEWITHSUNDEEP
sqlsql-server
vivienne
vivienne

Reputation: 41

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    |
+----+-------+--------+--------------+

Upvotes: 0

Views: 63

Answers (3)

Liya Tansky
Liya Tansky

Reputation: 249

select 
    debit.id, debit.debit, credit.credit, 
    debit.debit - credit.credit as [debit-credit]
from 
    debit 
left join 
    credit on debit.id = credit.id

BUT this will be based only on debit: meaning if you have id in credit which is not in debit it won't appear in this result.

Upvotes: 0

WLiu
WLiu

Reputation: 486

You can try "Left Join"

Select * 
from debit d 
left join credit c on d.id = c.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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.

Upvotes: 4

Related Questions