MySQL查询同一个表不相同列,使用order by和limit-mysql在单个查询中更新两个不同表的两列...

Query

UPDATE vcd_resorts AS resorts,

vcd_deals AS deals

SET resorts.rst_live_date = Date_add(Curdate(), INTERVAL 4 day),

deals.del_date = Date_add(Curdate(), INTERVAL 4 day)

WHERE 0 = (SELECT resort_id_count

FROM (SELECT Count(rst_id) AS resort_id_count

FROM vcd_resorts

WHERE rst_supersaver_resort = 1

AND rst_live_date BETWEEN Curdate() + 1 AND

Curdate() + 4)

temp)

AND resorts.rst_supersaver_resort = 1

AND resorts.rst_id = deals.del_resort_id

AND deals.del_supersaver_deal = 1

ORDER BY resorts.rst_live_date ASC

LIMIT 1

Error

#1221 - Incorrect usage of UPDATE and ORDER BY

what is wrong in this and any other way to do this

解决方案

i got solution of this question

UPDATE vcd_resorts AS resorts,

vcd_deals AS deals

SET resorts.rst_live_date = Date_add(Curdate(), INTERVAL 4 day),

deals.del_date = Date_add(Curdate(), INTERVAL 4 day)

WHERE 0 = (SELECT resort_id_count

FROM (SELECT Count(rst_id) AS resort_id_count

FROM vcd_resorts

WHERE rst_supersaver_resort = 1

AND rst_live_date BETWEEN Curdate() + 1 AND

Curdate() + 4)

temp)

AND resorts.rst_supersaver_resort = 1

AND resorts.rst_id = deals.del_resort_id

AND deals.del_supersaver_deal = 1

AND resorts.rst_id = (SELECT resort_id

FROM (SELECT rst_id AS resort_id

FROM vcd_resorts

WHERE rst_supersaver_resort = 1

ORDER BY rst_live_date ASC

LIMIT 1) temp1)

i have placed one more condition which replaced order by and limit