我有两个表Events和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);//////////////////////////////////////////////////////////////
若要访问固定位置数据,请使用以下语句
SELECT
Places,
FpOddsPrice
FROM FixedPlace as
WHERE NumberOfRunners <= :FNumberOfRunners
ORDER BY NumberOfRunners desc
LIMIT 1也就是说,如果:FNumberOfRunners = 11,那么Place为3,FpOddsPrice = 0.16
//////////////////////////////////////////////////////////////
我在编写一个更新语句时遇到了问题,它将根据FixedPlace表中的值更新‘Events’表
这不管用
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它给出了以下错误‘’更新订单的错误用法
,我该怎么做才能让它起作用?
编辑
这是可行的,但有更好的方法吗?
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发布于 2009-10-31 12:54:31
来自MySQL更新语法文档
对于多表语法,更新在table_references中命名的每个表中满足条件的行。在这种情况下,ORDER不能使用。
发布于 2009-10-31 12:58:38
这有点困难,因为MySQL不允许在子查询中使用LIMT。
我要做的是两个独立的查询。第一个已在您的帖子中;第二个将是一个简单的更新查询,如:
UPDATE Events E
SET E.Places = :Places
, E.FpOddsPrice = :Price
WHERE E.idEvents = :FidEventshttps://stackoverflow.com/questions/1654288
复制相似问题