Clever Geek Handbook

Fix query to mysql

I need to update a table from another table. I have multiple tables

   Table people

    |id|level| 
      1   0 
      2   0 
      3   0

and board games

|id | idman | win_or_lose | game |
    1     1        win        1
    2     2        lose       1
    3     1        win        2
    4     3        lose       2
    5     2        win        3
    6     3        lose       3

I need to create a query that will change the level in the people table. I am trying to use this query. but this is wrong. Help me fix this request.

My request.

UPDATE people p, 
(select count(id) as count
from games where and idman = p.id and win_or_lose = 'win') g
SET p.level = g.count
+3


source to share


3 answers


I would do it like this: ( Fiddle: http://sqlfiddle.com/#!2/1c29e/1/0 )



update people p join (select idman, count(*) as max_game
                        from games
                       where win_or_lose = 'win'
                       group by idman) x on p.id = x.idman
   set p.level = x.max_game
0


source


You can try this (I believe idman is the Id FK column of people)

UPDATE 
people 
JOIN (SELECT idman FROM games WHERE win_or_lose="win" GROUP BY game)a
  ON people.id=a.idman
SET level=level+1
;


If the same people win several different games. The level counter will keep increasing.

0


source 


UPDATE people p, 
(select count(id) as count
from games where idman = p.id and win_or_lose = 'win') g
SET p.level = g.count

just try this you added and in the where where without any conditions that would cause the problem, otherwise you are correct.

0


source






More articles:


All Articles