[SQL] 단지 SQL을 사용하여 반환 사전 UPDATE 열 값

단지 SQL을 사용하여 반환 사전 UPDATE 열 값

나는 관련 질문을 게시, 그러나 이것은 내 퍼즐의 또 다른 부분이다.

트리거를 사용하지 않고 (나 저장 프로 시저를하거나 다른 추가, 비 - SQL / - 쿼리 개체) - 나는 업데이트 된 행에서 열의 이전 값을 좀하고 싶습니다.

나는이 같은 쿼리가 :

   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;

내가 서브 쿼리의 끝에서 MY_TABLE ON UPDATE를 위해 할 수 있다면, 그 신성 (내 기타 질문 / 문제를 해결하는) 것입니다. 그러나 그것은 작동하지 않습니다 : (카운트 파악에 필요한) GROUP BY와 함께이 결합 할 수 없습니다. 그럼 난 그냥 그 trans_nbr의 걸릴 수와 (곧-BE-) 전 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 아니다; 복귀 절은 장님이다.

나는이 만든 모든 시도의 손실 계수 이후 긴했습니다; 말 그대로 시간이 연구되고있다.

서브 쿼리가 발생했을 때 만 행 및 - - 난 그냥 내 하위 쿼리의 행을 잠글 수있는 방탄 방법을 찾을 수 있다면 나는 사라질 것 피하기 위해 노력하고있어 모든 동시성 문제를 ...

업데이트 : 위의 제네릭이 아닌 코드에 오타가 있었다. 어윈 Brandstetter가 작동한다 제안 후 나는 시도. 그것은 이러한 종류의 솔루션을 찾기 위해 너무 오래 걸렸다 때문에, 아마 내 당황은 가치가있다? 적어도이 ... 이제 후손을 위해 SO에 있습니다>

내가 지금 (작품 것을)가 무엇과 같이이다 :

   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

카운트 (*)는 내 다른 (위 링크) 질문의 댓글에서 Flimzy에서 제안 당이다.

제대로 동시성 심지어 비 차단 버전을 구현하기위한 내 다른 질문을 참조하십시오; 업데이 트에서 이전 및 새 값을 얻을 수있는 방법이 쿼리는 단지 쇼, 나쁜 / 잘못된 동시성 비트를 무시합니다.

해결법

1. ==============================

   1.수동 설명 :

   수동 설명 :

   굵게 강조 광산. 돌아 오는 절에서 이전 행에 액세스 할 수있는 방법은 없습니다. 당신은 @Flimzy으로 트랜잭션에 싸여 @wildplasser 주석, 또는 @MattDiPasquale 게시 등의 CTE에 싸여 업데이트하기 전에 트리거 또는 별도의 SELECT와 함께이 제한 해결.

   그러나 완벽하게 정상적으로 당신이 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의 버전이 테스트.

   이 INSERT에 대해 서로 다른입니다 :

   같은 행에 동시 쓰기 작업과 경쟁 조건을 피하기 위해 여러 가지 방법이 있습니다. (. 관련이없는 행에 동시 쓰기 작업은 전혀 문제가 없다는 것을 주) 단순한는 느리고 확인 (하지만 비싼) 방법은 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 조건은 잠시 후에 행의 다른 세트로 해결할 수 있습니다.

   보다:

   DB <> 바이올린 여기 올드 sqlfiddle

2. ==============================

   2.당신은 SELECT 하위 쿼리를 사용할 수 있습니다.

   당신은 SELECT 하위 쿼리를 사용할 수 있습니다.

   예 : 이전 값을 반환하는 사용자의 이메일을 업데이트합니다.

3. ==============================

   3.@MattDiPasquale에 의해 제안 된 같은 CTE의 변형도 작동합니다. CTE를 I의 편안한 수단으로, 더 명확하지만 것입니다 :

   @MattDiPasquale에 의해 제안 된 같은 CTE의 변형도 작동합니다. CTE를 I의 편안한 수단으로, 더 명확하지만 것입니다 :

   WITH sel AS (
      SELECT tbl_id, name FROM tbl WHERE tbl_id = 3  -- assuming unique tbl_id
      )
   , upd AS (
      UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3
      RETURNING tbl_id, name
      )
   SELECT s.tbl_id AS old_id, s.name As old_name
        , u.tbl_id, u.name
   FROM   sel s, upd u;
   

   테스트없이 나는이 작품을 주장 : SELECT 및 UPDATE가 동일한 데이터베이스 스냅 샷을 참조하십시오. 선택은 UPDATE가 정의하여 새 값을 반환하면서, (당신이 UPDATE와 CTE 후 CTE를 배치하는 경우에도) 이전 값을 반환 할 수밖에 없다. 짜잔.

   그러나 그것은 내 첫 번째 대답보다 느리게 될 것입니다.

4. ==============================

   4.이 딜레마에 직면했을 때 나는 테이블에 정크 열을 추가하고 내가 기록을 업데이트 할 때 그때는 (그때 반환하는) 정크 열에 이전 값을 복사합니다. 이 표를 조금 bloats하지만 대한 조인의 필요성을 방지 할 수 있습니다.

   이 딜레마에 직면했을 때 나는 테이블에 정크 열을 추가하고 내가 기록을 업데이트 할 때 그때는 (그때 반환하는) 정크 열에 이전 값을 복사합니다. 이 표를 조금 bloats하지만 대한 조인의 필요성을 방지 할 수 있습니다.

from https://stackoverflow.com/questions/7923237/return-pre-update-column-values-using-sql-only by cc-by-sa and MIT license