Help with INNER JOIN problem (MySQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Help with INNER JOIN problem (MySQL)

Post 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?
Last edited by Jean-Yves on Tue Jan 11, 2005 10:12 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

cheers feyd, I'll give that a try (once my flu + conjunctivitis clear up! )
Post Reply