如何为此 SELECT 查询编写 UPDATE 查询?

发布于 2024-11-30 20:15:32 字数 947 浏览 3 评论 0原文

我有一个来自上一个问题的查询(链接) 关于复杂的选择, 我的问题是,如何为此查询的结果编写更新查询,以将 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

嗼ふ静 2024-12-07 20:15:32

如果您尝试更新集合中的所有记录,则可以编写如下查询:

UPDATE message_queue mq
INNER JOIN (
   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 )
) msgs ON msgs.msg_id = mq.msg_id
SET mq.fet_id = 12345;

但是,根据您的活动,这不是一种安全的方法,因为从查询返回的记录可能会在两个请求之间发生变化。

我建议使用以下方法之一:

  • 在应用程序端处理更新
  • 使用查询结果创建一个临时表,并通过与临时表的联接更新该表
  • 假设“fet_id”是该批次的唯一键,首先运行更新语句,然后根据 fet_id 进行简单的选择。

If you're trying to update all the records within the set, you could write a query like this:

UPDATE message_queue mq
INNER JOIN (
   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 )
) msgs ON msgs.msg_id = mq.msg_id
SET mq.fet_id = 12345;

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:

  • Handle the updates on the application side
  • Create a temporary table with the result of the query, and update the table with a join to the temporary table
  • Assuming 'fet_id' is a key unique to this batch, run the update statement first, then do a simple select based on fet_id.
暗地喜欢 2024-12-07 20:15:32

我发现您正在询问 MySql。这是一个 MsSql 解决方案,但我认为语法非常接近(但不能 100% 确定)。此示例经过简化,因此可以适用于任何 Stack 用户。希望您可以转换为您的特定数据集。

-- sample table
create table x 
    (col1 int identity(1, 1)
    ,col2 varchar(50))

-- sample data  
insert into x (col2) values
     (null)
    ,(null)
    ,(null)
    ,(null)
    ,(null)

-- update from select
update x
    set x.col2 = 'even'
from x as [t2]
where 
    col1 = t2.col1
    and t2.col1 % 2 = 0

-- show results
select * from x

-- clean up
drop table x

祝你好运。

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.

-- sample table
create table x 
    (col1 int identity(1, 1)
    ,col2 varchar(50))

-- sample data  
insert into x (col2) values
     (null)
    ,(null)
    ,(null)
    ,(null)
    ,(null)

-- update from select
update x
    set x.col2 = 'even'
from x as [t2]
where 
    col1 = t2.col1
    and t2.col1 % 2 = 0

-- show results
select * from x

-- clean up
drop table x

Best of luck.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文