CODEWITHSUNDEEP
sqlsql-server-2005
gruber
gruber

Reputation: 29749

update table with join but with summed values

I've got 2 tables:

-Card
id
points

-History
CardId
points

Now I would like to perform update query which subtracts points in Card table based on points in History with the same cardId

for example I have rows:

-card
1 10
2 30

-History
1 5
1 3
2 10 
2 9

and as a result I should have in Card table rows:

-card
1 2
2 11

what is the best way to do that?

Upvotes: 0

Views: 57

Answers (1)

podiluska
podiluska

Reputation: 51504

This will do it.

update card
set points = points - total
from card
    inner join (select cardid, sum(points) as total from history) v 
    on card.id = v.cardid

But I agree with other comments questioning your database structures

Upvotes: 1

Related Questions