update multiple tables 1 query

[jazzman1]

Yep, I finaly made a test with a real data and so you are right

 

But....why we should not able to make a join tables in one Case operator?

Edited May 12, 2013 by jazzman1

[Barand]

A CASE statement simply returns a value depending on the conditions. Nothing to do with joins.

[Barand]

On the other hand, if you had fixtures table

 

id | hometeam | awayteam | homegoals | awaygoals

 

and you want the names of the winning teams

SELECT f.hometeam, f.awayteam, f.homegoals, f.awaygoals,
    IFNULL(t.team_name, 'DRAW') as Winner
FROM fixture f
    LEFT JOIN teams t ON t.team_id =
    CASE
        WHEN homegoals > awaygoals THEN hometeam
        WHEN homegoals < awaygoals THEN awayteam
        ELSE NULL
    END

+----------+----------+-----------+-----------+--------+
| hometeam | awayteam | homegoals | awaygoals | Winner |
+----------+----------+-----------+-----------+--------+
|        4 |        2 |         1 |         0 | Team 4 |
|        2 |        4 |         2 |         2 | DRAW   |
|        3 |        2 |         4 |         4 | DRAW  |  
|        1 |        3 |         1 |         1 | DRAW   |
|        2 |        3 |         1 |         2 | Team 3 |
|        3 |        1 |         1 |         3 | Team 1 |
|        4 |        3 |         2 |         0 | Team 4 |
|        2 |        1 |         0 |         3 | Team 1 |
|        1 |        4 |         2 |         4 | Team 4 |
|        4 |        1 |         4 |         4 | DRAW   |
|        1 |        2 |         4 |         1 | Team 1 |
|        3 |        4 |         1 |         4 | Team 4 |
+----------+----------+-----------+-----------+--------+

Edited May 12, 2013 by Barand