我想写一个存储过程,想修改每种分类的最新的5条数据。下面是我写的存储过程
BEGIN
DECLARE atId INT;
DECLARE ids CURSOR for select id FROM house_agent;
OPEN ids;
WHILE stop <> 1 DO
FETCH ids INTO atId;
UPDATE house_rent SET recommend=TRUE WHERE id IN (SELECT id FROM house_rent where agentId = atId and deleted=FALSE );
END WHILE;
END
上面sql语句保存时不报错,但是当我加上limit 限制时就报错,说IN 和limit 不能共用。
UPDATE house_rent SET recommend=TRUE WHERE id IN (SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5);急切需要大侠们的帮助,高分求解,我用的mysql是5.0的。
BEGIN
DECLARE atId INT;
DECLARE ids CURSOR for select id FROM house_agent;
OPEN ids;
WHILE stop <> 1 DO
FETCH ids INTO atId;
UPDATE house_rent SET recommend=TRUE WHERE id IN (SELECT id FROM house_rent where agentId = atId and deleted=FALSE );
END WHILE;
END
上面sql语句保存时不报错,但是当我加上limit 限制时就报错,说IN 和limit 不能共用。
UPDATE house_rent SET recommend=TRUE WHERE id IN (SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5);急切需要大侠们的帮助,高分求解,我用的mysql是5.0的。
UPDATE house_rent A Inner Join
(SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5)T On A.ID=T.ID
SET A.recommend=TRUE
set a.recommend=TRUE
where a.id=b.id
(SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5) b On A.ID=b.ID
SET A.recommend=TRUE
(SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5)T On A.ID=T.ID的速度如何?ID是不是主键? SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5的速度如何?如果很慢,则贴出
explain select * from house_rent A Inner Join
(SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5)T On A.ID=T.ID
的分析结果。
贴建表及插入记录的SQL,及要求结果出来看看
看看能否用SQL语句解决
UPDATE house_rent a, (SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5) b
set a.recommend=TRUE
where a.id=b.idTime: 989.140ms第二种UPDATE house_rent A Inner Join
(SELECT id FROM house_rent where agentId = atId and deleted=FALSE LIMIT 5)T On A.ID=T.ID
SET A.recommend=TRUETime: 1033.937ms
数据量不是的很多,修改4k多条数据。谢谢大家的帮助,今天结贴。
经纪人,发布房产信息,一个表是出租,一个是出售,现在我要把采集的数据,每个经纪人的最新的房产信息的前5条记录更新为推荐,既:recommend设置为true。