CODEWITHSUNDEEP
mysql
user2908294
user2908294

Reputation: 51

MySQL Update a table from another table

I have two tables named history and history detail. The history table provides me a count of gym access by month, so there is duplicate users. I am wanting to update the history detail table to reflect what months users accessed the gym. I have updated the history detail table making the user name unique so there is no duplicates. Below is an example of what I am trying to achieve in the history detail table.

History

| Gym Use | Month |
| Jones | April |
| Smith | April |
| Jones | March |
| Jones | Febru |
| Smith | Janua |

History Detail

| Gym Use | Jan | Feb | Mar | April |
| Jones _ | _ | X | X | X |
| Smith _ | X | _ | _ | X |

Upvotes: 0

Views: 42

Answers (2)

krokodilko
krokodilko

Reputation: 36087

Just do a pivot query:

SELECT `Gym Use`,
       min(IF( `Month` = 'April', 'X', '_' )) As April,
       min(IF( `Month` = 'March', 'X', '_' )) As March,
       min(IF( `Month` = 'Febru', 'X', '_' )) As Febru,
       min(IF( `Month` = 'Janua', 'X', '_' )) As Janua
FROM history
GROUP BY `Gym Use`

Demo: http://sqlfiddle.com/#!9/f71c5/1

and use a result of this query in multitable update:

UPDATE `history detail` h, (
   SELECT `Gym Use`,
          min(IF( `Month` = 'April', 'X', '_' )) As April,
          min(IF( `Month` = 'March', 'X', '_' )) As March,
          min(IF( `Month` = 'Febru', 'X', '_' )) As Febru,
       min(IF( `Month` = 'Janua', 'X', '_' )) As Janua
   FROM history
   GROUP BY `Gym Use`
) x
SET h.April = x.April, h.March = x.March, h.Febru = x.Febru, h.Janua = x.Janua
WHERE h.`Gym Use` = x.`Gym Use`

Demo: http://sqlfiddle.com/#!9/4c153/1

Upvotes: 1

Tomaso Albinoni
Tomaso Albinoni

Reputation: 1013

UPDATE history_detail SET Jan='X'
WHERE Gym_User IN (SELECT Gym_User FROM history WHERE Month='Janua');
UPDATE history_detail SET Feb='X'
WHERE Gym_User IN (SELECT Gym_User FROM history WHERE Month='Febru');

etc.

Upvotes: 0

Related Questions