Page 1 of 1

Help with INNER JOIN problem (MySQL)

Posted: Tue Aug 10, 2004 8:16 am
by Jean-Yves
Can anyone see what's wrong with this piece of SQL? It runs fine on my dev PC (WinXP Pro, PHP 4.3.8, MySQL 3.23.49), but not on the live server(RH Linux, PHP 4.3.2, MySQL 3.23.58)

As a summary, it is used in an online fantasy strategy game to update citadels for races that can ride(eg men and elves as opposed to dwarves, giants, etc).

Code: Select all

UPDATE Lords INNER JOIN Races ON RaceID = LordRaceID 
  SET Riders = Riders + 5, Warriors = Warriors + 10 
  WHERE GameID = 67 
    AND UserID <> 0 
    AND INSTR('CY', Garrison) > 0 
    AND Riders + Warriors < 2500 
    AND RaceCanRide = 1
The error lies with the inner join. Removing this makes the SQL work, although I must then also remove the "AND RaceCanRide = 1" condition, so dwarves and giants get riders.

As I said earlier, this code works fine onmy dev PC.

Any ideas?

Posted: Tue Aug 10, 2004 10:16 am
by feyd
try something like this:
(untested)

Code: Select all

UPDATE Lords a, Races b SET a.Riders = a.Riders+5, a.Warriors = a.Warriors + 10
WHERE b.RaceID = a.LordRaceID AND a.GameID = 67 AND a.UserID != 0 AND INSTR('CY',a.Garrison) > 0 AND a.Riders + a.Warriors < 2500 AND b.RaceCanRide = 1
however, I'm pretty sure you can't do the join:
Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:

Code: Select all

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Posted: Thu Aug 12, 2004 9:19 am
by Jean-Yves
cheers feyd, I'll give that a try (once my flu + conjunctivitis clear up! )