如何为此 SELECT 查询编写 UPDATE 查询?
我有一个来自上一个问题的查询(链接) 关于复杂的选择, 我的问题是,如何为此查询的结果编写更新查询,以将 fet_id 更改为某个新值。
请注意,此查询将从队列中选择行,直到 rcpts_count 达到 1000(用于流量控制)。有关此查询的更多信息是,在此问题中
SELECT NULL AS msg_id, NULL AS total, NULL AS found
FROM dual
WHERE (
@total :=0
OR
@found :=0
)
UNION
SELECT msg_id, @total AS total, @found :=1 AS found
FROM messages_queue
WHERE (
@total := @total + rcpts_count
)
AND @total <1000
UNION
SELECT msg_id, rcpts_count AS total, 0 AS found
FROM messages_queue
WHERE IF( @found =0, @found :=1, 0 )
I have a query from my previous question (link) about a complex select,
and my question is, how can I write an update query for result of this query, to change fet_id to some new value.
Note that this query will select rows from a queue until rcpts_count reaches 1000, (for traffic controlling). more information about this query is, in this question
SELECT NULL AS msg_id, NULL AS total, NULL AS found
FROM dual
WHERE (
@total :=0
OR
@found :=0
)
UNION
SELECT msg_id, @total AS total, @found :=1 AS found
FROM messages_queue
WHERE (
@total := @total + rcpts_count
)
AND @total <1000
UNION
SELECT msg_id, rcpts_count AS total, 0 AS found
FROM messages_queue
WHERE IF( @found =0, @found :=1, 0 )
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果您尝试更新集合中的所有记录,则可以编写如下查询:
但是,根据您的活动,这不是一种安全的方法,因为从查询返回的记录可能会在两个请求之间发生变化。
我建议使用以下方法之一:
If you're trying to update all the records within the set, you could write a query like this:
However, depending on your activity, this isn't a safe approach, as the records returned from the query could change between the two requests.
I would recommend one of the following approaches:
我发现您正在询问 MySql。这是一个 MsSql 解决方案,但我认为语法非常接近(但不能 100% 确定)。此示例经过简化,因此可以适用于任何 Stack 用户。希望您可以转换为您的特定数据集。
祝你好运。
I see that you are asking for MySql. This is an MsSql solution, but I think the syntax is really close (but not 100% sure of that). This sample is simplified so it can apply to any Stack user. Hopefully, you can translate to your specific dataset.
Best of luck.