如何在不删除唯一性验证的情况下在单个SQL语句中运行多个UPDATE

Question

一个book has_many pages。页面具有属性page_no。我想在书的任意两页（x，y）之间添加PageAt（z），为此，我必须首先将书的所有页面从y更新到book.pages.count，然后创建->保存页面（z）。

以下是单次运行更新的SQL：

sql = "update pages set page_no = page_no+1 where book_id =" + (@book.id).to_s + " and page_no >" + (@addPageAt - 1).to_s
records_arraty = ActiveRecord::Base.connection.execute(sql)

它有效！但要做到这一点，我必须从pages表中删除以下验证：

validates :book_id, presence: true, on: :save
validates :page_no, presence: true, uniqueness: true, on: :save
validates_uniqueness_of :page_no, :scope => :book_id

我想保留这些验证，特别是第三个验证。如何做到这一点？

Answer 1

您需要：

1. 在运行上述Gordon的SQL时使用SERIALIZABLE事务（并让调用者预期失败（以防其他人尝试冲突事务），从而循环重试事务

或

2. 在一个SQL语句中运行两个UPDATE，如下所示：

   "WITH a
   AS (
     UPDATE pages
     SET page_no = - (page_no + 1)
     WHERE book_id = " + (@book.id).to_s + "
       AND page_no > " + (@addPageAt - 1).to_s + "
     RETURNING *
   ), b AS (
     UPDATE pages
     SET page_no = - page_no
     WHERE book_id = " + (@book.id).to_s + " 
       AND page_no < 0
     RETURNING *
   )
   SELECT 1
   FROM a, b
   LIMIT 1;"
   

Answer 2

一种方法是两个独立的更新：

update pages
    set page_no = - (page_no + 1)
    where book_id = " + (@book.id).to_s + " and page_no >" + (@addPageAt - 1).to_s;
update pages
    set page_no = - page_no
    where where book_id = " + (@book.id).to_s " and page_no < 0);

您可以在单个事务中执行此操作，因此负数永远不会可见。

Answer 3

您可以尝试将ORDER BY page_no DESC添加到更新查询中。从最后一个到第一个工作时不会有重复的page_no。