尝试更新特定选择的结果时出现问题

Question

我有以下查询，可以选择折扣产品注册。这非常有效。

SELECT * FROM products AS r1 
JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM products)) AS id) AS r2 
WHERE r1.id >= r2.id 
AND discount_rate <> 0 
ORDER BY r1.id ASC LIMIT 15

好的，但是我该如何在这些记录中填写信息呢。我试图表明他们能够出现在特别优惠部分。根据有效的查询，我编写了以下不起作用的更新查询：

UPDATE products 
SET is_special_offer=1 
WHERE IN (SELECT * FROM products AS r1 
JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM products)) AS id) AS r2 
WHERE r1.id >= r2.id 
AND discount_rate <> 0 
ORDER BY r1.id ASC LIMIT 15) 

错误"操作数应包含1列"；

我不知道如何更新这些结果。有人能帮我吗？

Answer 1

在MYSQL中，您可以将select转换为update，如下所示：

update products p
inner join (select ceil(rand() * max(id)) as id from products) p1 on p1.id >= p.id 
set p.is_special_offer = 1
where p.discount_rate <> 0 
order by p.id 
limit 15

MySQL在更新查询中确实支持orderby/limit。