当前位置: 首页 > 面试题库 >

仅使用SQL返回更新前的列值

闻人嘉木
2023-03-14
问题内容

我想从已更新的行中获取列的OLD值-无需使用触发器(也无需使用存储过程,也无需使用任何其他额外的非-SQL / -query实体)。

我有这样的查询:

   UPDATE my_table
      SET processing_by = our_id_info  -- unique to this worker
    WHERE trans_nbr IN (
                        SELECT trans_nbr
                          FROM my_table
                         GROUP BY trans_nbr
                        HAVING COUNT(trans_nbr) > 1
                         LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id;

如果我可以FOR UPDATE ON my_table在子查询的末尾做这件事,那真是太了不起了(并解决了我的其他问题/问题)。但这是行不通的:不能将其与GROUP BY(这对于计算计数是必需的)结合使用。然后,我可以采用那些trans_nbr的值,并先执行查询以获取(即将被)以前的processing_by值。

我试过像这样:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table old_my_table
     JOIN (
             SELECT trans_nbr
               FROM my_table
           GROUP BY trans_nbr
             HAVING COUNT(trans_nbr) > 1
              LIMIT our_limit_to_have_single_process_grab
          ) sub_my_table
       ON old_my_table.trans_nbr = sub_my_table.trans_nbr
    WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
      AND my_table.processing_by = old_my_table.processing_by
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

但这是行不通的。old_my_table在联接外部不可见;该RETURNING条款对它是盲目的。

从那以后,我已经失去了所有尝试的机会。我已经研究了好几个小时。

如果我可以找到一种防弹方法来锁定子查询中的行-并且仅锁定那些行以及子查询发生时-我试图避免的所有并发问题都将消失…

更新:我在上面的非通用代码中有一个错字。在Erwin Brandstetter建议它可以工作之后,我重试了。既然我花了这么长时间找到这种解决方案,也许我的尴尬值得吗?至少现在是后代了…:>

我现在拥有的(有效的)是这样的:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table AS old_my_table
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
      AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

COUNT(*)是Flimzy在对我的其他问题(在上面链接)的评论中提出的建议。

请参阅我的其他问题,以正确实现并发甚至是非阻塞版本;此查询仅显示如何从更新中获取旧值和新值,而忽略错误/错误的并发位。


问题答案:

问题
手册说明:

可选RETURNING子句导致UPDATE基于实际更新的每一行计算并返回值。FROM可以计算使用表的列和/或中提到的其他表的列的任何表达式。使用表列的新(更新后)值。该RETURNING列表的语法与的输出列表的语法相同SELECT。

大胆强调我的。无法访问RETURNING子句中的旧行。您可以通过触发器来解决此限制,也可以使用@SELECT 之前的分隔UPDATE符来解决此限制,如@Flimzy和@wildplasser所注释,将其包装在事务中,或如@MattDiPasquale发布,将其包装在CTE中。

没有并发写入的解决方案
但是,如果您在子句中加入表的另一个实例,则您要实现的目标效果很好FROM:

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

返回值:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

用于自联接的列必须为UNIQUE NOT NULL。在简单的示例中,WHERE条件在同一列上tbl_id,但这只是巧合。适用于任何条件。

我使用8.4到13的PostgreSQL版本进行了测试。

并发写入负载的解决方案
有多种方法可以避免在同一行上进行并发写入操作时出现竞争情况。(请注意,对不相关的行进行并发写入操作完全没有问题。)一种简单,缓慢且确定(但昂贵)的方法是以SERIALIZABLE隔离级别运行事务:

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ... ;
COMMIT;

但这可能太过分了。并且您需要准备在序列化失败的情况下重复操作。

简单和快速(和一样并发写入负载可靠)是在明确的锁定一个行进行更新:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

请注意WHERE条件如何移动到子查询(再次可以是任何东西),并且仅自我联接(在UNIQUE NOT NULL列上)保留在外部查询中。这保证了仅SELECT处理由内部锁定的行。WHERE稍后,条件可能会解决为一组不同的行。



 类似资料:
  • 问题内容: 有没有什么术语可以返回表的前两个表而 又不 知道字段 名 ? 就像是 还是我必须走很长的路要走,首先要找出列名?我该怎么做? 问题答案: 您必须先获取列名称。大多数平台都支持:

  • 问题内容: 我有一个查询,返回以下行: StateId,OrderId,OrderTime,PermitId 我只需要返回全盘完全重复的行,因此每条记录必须与另一条记录完全相同,这样它才可以重复。我想退回两个记录。这些记录与一堆没有重复的记录混在一起… 任何的想法? 问题答案: 首先,确定重复项。其次,重新加入以提取这些行。 非聚集(或非窗口/排名)自连接形成部分交叉连接,并为任何一组键给出重复的

  • 问题内容: 我有下表所示的表 我们有包含“领导者ID”的表 问题陈述: 此问题是通过使用Employee及其Manager数据来确定Manager的负责人。 关于:我们有一个员工ID和他们的经理ID。请注意,经理ID来自员工ID。由于每位经理都有一个高于其级别的经理。 首先,我们将在Manager ID列中获取所有UNIQUE ID。然后,对于Manager ID列中的每个ID,我们将查找其各自的

  • 问题内容: 我写了一个具有以下查询的SQL脚本。查询工作正常。 但是现在,我不想在查询本身中编写大约100个名称,而是要从CSV文件中获取所有名称。我在互联网上读到有关SQL * Loader的信息,但在更新查询上却得不到很多。我的csv文件仅包含名称。 我试过了 我怎样才能做到这一点?提前致谢。 问题答案: SQL * Loader不执行更新,仅执行插入。因此,您应该将名称插入一个单独的表(例如

  • 问题内容: 我有以下查询,仅在它们为空时才在其中更新值。 是否可以将所有这些都放入单个查询中? 和 问题答案: 您可以尝试: 尽管它可能会触发更新触发器,即使对于实际上未更改的行也是如此。