Update total by Count the amount of times the id appears in database

So I want to make sure that in my ritten tabel when a 20 as id appears for 4 times that the ritten_total changes to 4 I have tried this:

UPDATE ritten, users
SET ritten_totaal = ritten_totaal + 1;

I also tried suming the id behind the ritten_totaal = that resulted in an error

I have two tables one called ritten one called users their linked not with id but with rit_cc now my desired result is that when i add a new rit to the id of a user that the total of ritten_total goes up

Sample data (dont know if im supposed to do it like this doh):

ritten:

id       ritten_totaal         rit_cc 
64       1                     GeJo 
65       1                     PeT2

users:

id    rit_cc 
4     PeT2 
20    GeJo

SELECT ritten_totaal, u.id 
FROM ritten r 
JOIN users u ON r.rit_cc = u.rit_cc

ritten_totaal id
1             20
1             20
1             21
1             18
1             20
1             20
1             4
1             4
1             19
1             19

So every 20 id in this list should increase the value of ritten_totaal there are 4 20s in this list so ritten_totaal should UPDATE to 4. when another rit gets added whit the same id into ritten it should increase to 5

SELECT COUNT(ritten_totaal)ritten_totaal, u.id
FROM ritten r 
JOIN users u 
ON r.rit_cc = u.rit_cc 
WHERE u.id = 20;

ritten_totaal id
4             20

Something like this i need it to update for every id

Answer

I think you want:

UPDATE users u
    SET ritten_totaal = (SELECT COUNT(*)
                         FROM ritten r
                         WHERE r.rit_cc = u.rit_cc
                        );

Advertisement

Answer