update语句with limit

Question

update accounts a
left join users b on
    a.user_id = b.user_id
set `sold` = '1' where b.country = 'UA' AND a.site = 'od'

如何限制此查询？

已尝试

update accounts a
left join users b on
    a.user_id = b.user_id
set `sold` = '1' where b.country = 'UA' AND a.site = 'od' LIMIT 500

但get错误错误: UPDATE和LIMIT的用法不正确

Answer 1

尝尝这个

update accounts a left join users b on a.user_id = b.user_id
set 'sold' = '1'
where b.country in
(select country from
(select country from users where
country = 'UA' order by user_id asc limit 500) tmp) and 
a.site in
(select site from
(select site from accounts where
site = 'od' order by user_id asc limit 500) tmp2);

有关select limit语句的详细信息，请参阅此部分。

http://www.techonthenet.com/sql/select_limit.php

Answer 2

我将首先使用一个CTE表(临时表)来保存您的限制查询，然后使用该表执行更新：

With LimitQuery As
(
select * from  accounts a
left join users b on
    a.user_id = b.user_id
where b.country = 'UA' AND a.site = 'od' LIMIT 500
)

update LimitQuery 
set `sold` = '1'