时间戳

首页 > 解决方案 > 在同一查询或触发建议中插入和更新

sql - 在同一查询或触发建议中插入和更新

问题描述

我想知道这是否是以这种方式插入和更新的正确方法，我认为缺点是更新中的 payout_date 与插入事务表不完全相同，在大数据集中它可能是个问题。 ..

该函数只是将符合条件的支出放到交易表中，然后由 java 应用程序进一步处理。我想同时将 payout_date 放入支付表中，这表明它已经被移动了。

或者我应该使用触发器？但我不知道如何为这种情况构建它。

这是示例数据集和代码：

https://www.db-fiddle.com/f/kNn3c3F96nqEwHoq9biF81/1

create table payouts 
(payout_id integer,
 customer_id integer,
 update_time timestamptz,
 amount integer,
 is_blocked boolean,
 payout_date timestamptz,
 paid_by integer);

 create table users
 (customer_id integer,
  account_number integer);

  create table transactions
  (transaction_id serial, 
   transaction_type integer,
   transaction_date timestamptz,
   account_number integer,
   reference_id integer,
   amount integer, 
   external_id integer);

    insert into payouts (payout_id, customer_id, update_time, amount, is_blocked)
    values 
    (1,1000, now() - interval '5 hours', 323, FALSE),
     (2,1005, now() - interval '5 hours', 66, FALSE),
      (3,1077, now() - interval '5 hours', -200, FALSE),
       (4,1077, now() - interval '5 hours', 88, TRUE),
        (5,2112, now() - interval '5 hours', 153, FALSE);

       insert into users
       values
       (1000, 2000),
        (1005, 2005),
         (1077,2077),
          (2112,3112),
           (1033,2033);




INSERT INTO transactions
( transaction_type
, transaction_date
, account_number
, reference_id
, amount)

SELECT
    100
  , now()
  , u.account_number
  , p.payout_id
  , p.amount 
  FROM
  payouts p
  JOIN users u ON u.customer_id = p.customer_id
  LEFT JOIN transactions t ON t.reference_id = p.payout_id and t.account_number = u.account_number and t.transaction_type = 100 
  WHERE payout_date is null and p.amount > 0  and is_blocked is FALSE
  AND t.account_number is null 
  AND t.reference_id is null;

  UPDATE payouts p 
  set
    payout_date = now()
  , paid_by = 111 -- variable in function

  FROM  payouts p2 
  JOIN users u ON u.customer_id = p2.customer_id
  LEFT JOIN transactions t ON t.reference_id = p2.payout_id and t.account_number = u.account_number and t.transaction_type = 100
  WHERE p.payout_id = p2.payout_id  
  AND p2.payout_date is null and p2.amount > 0  and p2.is_blocked is FALSE
  AND t.account_number is NOT null 
  AND t.reference_id is NOT null;

标签： sqltriggerspostgresql-9.5

解决方案

我设法在一批中创建插入和更新，使用returning

data_selected 为 (SELECT 100 , now() , u.account_number , p.payout_id , p.amount FROM payouts p JOIN users u ON u.customer_id = p.customer_id LEFT JOIN transactions t ON t.reference_id = p.payout_id and t .account_number = u.account_number 和 t.transaction_type = 100 其中 payout_date 为空且 p.amount > 0 且 is_blocked 为 FALSE 且 t.account_number 为空且 t.reference_id 为空；),

insert_data as (

INSERT INTO transactions
( transaction_type
, transaction_date
, account_number
, reference_id
, amount)

returning reference_id)


update payouts p
set
    payout_date = now()
  , paid_by = adminId

FROM insert_data id

where p.payout_id =  id.reference_id;

推荐阅读