Mariadb – LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3

As the MySQL documentation mentions at the bottom of the UPDATE documentation:

Currently, you cannot update a table and select from the same table in a subquery.

I've modified your query and I added another subquery:

update store_players as sp 
set sp.credits=sp.credits+5000 
where sp.name IN (
    select a.name from (select sp2.name from syndicate_store2.store_players as sp2,
    syndicate_ipb.nexus_purchases as np  
    left join syndicate_ipb.members as mem  
    on np.ps_member=mem.member_id  
    where sp2.authid = IntToSteam(mem.steamid)  
    and np.ps_item_id=15) AS a);

And without subqueries:

UPDATE test.store_players as sp 
JOIN syndicate_ipb.nexus_purchases AS np ON (np.ps_item_id=15)
LEFT JOIN syndicate_ipb.members as mem on (np.ps_member=mem.member_id)
SET sp.credits=sp.credits+5000 
WHERE sp.authid = IntToSteam(mem.steamid);

EDIT:

Query:

update store_players as sp 
JOIN (select sp2.name,COUNT(sp2.name) AS cnt 
        from syndicate_store2.store_players as sp2,
        syndicate_ipb.nexus_purchases as np  
        left join syndicate_ipb.members as mem  
        on np.ps_member=mem.member_id  
        where sp2.authid = IntToSteam(mem.steamid)  
        and np.ps_item_id=15
        group by sp2.name) AS q
    ON (q.name=sp.name)
set sp.credits=sp.credits+(5000*q.cnt);

PD: Replace the schema test for your store_players table schema.

Try it!

You are grouping by the thing you are counting, not by the department name. Change your group by to: group by b.deptname

Edit:Adding a little flavor text as requested.

Basically what is happening is you are choosing to aggregate one of the values (in this case the counts of the salary) so that it "rolls up", and the group by generally indicates which value you want to do the rolling up by.

It makes some sense to say "I want to group by the number of employees" but you are actually trying to express "I want to return the number of employees grouped by department."