CODEWITHSUNDEEP
sqlmysql
Charles Faiga
Charles Faiga

Reputation: 11763

How does one do a multi table UPDATE in MYSQL 5.1 using ‘ORDER BY’ and ‘LIMIT’ statments

I have two tables Events and FixedPlace

CREATE TABLE  `events` (
  `idEvents` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NumberOfPlaces` int(10) unsigned DEFAULT '0',
  `FpOddsPrice` double DEFAULT '0',
  PRIMARY KEY (`idEvents`),
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

////////////////////////////////////////////////////////////////

CREATE TABLE  ` fixedplace ` (
  `idFixedPlacePrice` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NumberOfRunners` int(10) unsigned DEFAULT NULL,
  `Places` int(10) unsigned DEFAULT NULL,
  `FpOddsPrice` double DEFAULT NULL,
  PRIMARY KEY (`idFixedPlacePrice`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;




Insert Into fixedplace (NumberOfRunners, Places, FpOddsPrice)
Values
(0, 0, 0),
(10, 3, 0.16),
(13, 4, 0.21);

//////////////////////////////////////////////////////////////

To access the fixed place data I use the following statement 

SELECT
  Places,
  FpOddsPrice
FROM FixedPlace as 
WHERE NumberOfRunners <=  :FNumberOfRunners 
ORDER BY NumberOfRunners desc
LIMIT 1

i.e. If :FNumberOfRunners = 11 then Place will be 3 and FpOddsPrice = 0.16

//////////////////////////////////////////////////////////////

I am having a problem writing an Update statement That will update ‘Events’ table based on the values in FixedPlace table

This does not work

UPDATE Events as E, FixedPlace as F 
Set E.Places       = F.Places,
    E.FpOddsPrice  = F.FpOddsPrice
WHERE E.idEvents = :FidEvents
   And F.NumberOfRunners <=  :FNumberOfRunners
ORDER BY F.NumberOfRunners desc
LIMIT 1

It gives the following error ‘Incorrect usage of UPDATE an ORDER BY’

What must I do to get this to work ?

EDIT

This works - but is there a better way of doing it

UPDATE Events as E
Set E.Places       = (Select Places     FROM FixedPlace WHERE NumberOfRunners <=  :FNumberOfRunners ORDER BY NumberOfRunners desc LIMIT 1),
    E.FpOddsPrice    = (Select FpOddsPrice FROM FixedPlace WHERE NumberOfRunners <=  :FNumberOfRunners ORDER BY NumberOfRunners desc LIMIT 1)
WHERE E.idEvents = :FidEvents

Upvotes: 0

Views: 364

Answers (2)

Andomar
Andomar

Reputation: 238196

This is kind of hard since MySQL does not allow LIMT in subqueries.

What I'd do is two separate queries. The first one is already in your post; the second would be a simple UPDATE query like:

UPDATE Events E
SET  E.Places = :Places
,    E.FpOddsPrice = :Price
WHERE E.idEvents = :FidEvents

Upvotes: 0

jitter
jitter

Reputation: 54605

From the MySQL UPDATE Syntax documentation

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

Upvotes: 2

Related Questions