我怎样才能使这个查询工作？

Question

我昨天发布了这个问题，由于某种原因我无法让它工作。我得到了很多好的答案，但它们不符合我需要的范围。基本上我需要运行一个查询，该查询将循环所有客户并获取他们的 Acct_Balance 并将其设置为 0，但在将其设置为 0 之前，我需要向 AR_Transactions 表添加一个具有负数的条目。因此，出于实数考虑，如果客户表中的 Acct_Balance 字段中有 -200，那么我需要添加一个条目或 200 来将该值变为 0。有点像将其清零的行项目。反之亦然，如果客户在客户表的 Acct_Balance 字段中有 200，那么我需要添加一个条目或 -200 将其清零。我尝试了一些事情。这是我尝试过的示例之一。

BEGIN TRANSACTION 
INSERT INTO [cresql].[dbo].[AR_Transactions] 
(Trans_ID, DateTime , Dirty, Store_ID, Trans_Type,  Cashier_ID,  CustNum,     Trans_Amount, Prev_Cust_Balance ) 
SELECT  (SELECT MAX(Trans_ID ) + 1 FROM [cresql].[dbo].[AR_Transactions]), DATEADD(MINUTE, -30, Getdate()), 1, 1001, 'C', 100199,     CustNum,     -Acct_Balance,   Acct_Balance 
FROM  [cresql].[dbo].[Customer] 
WHERE Acct_Balance <> 0  
UPDATE [cresql].[dbo].[Customer] SET Acct_Balance = 0 
WHERE Acct_Balance <> 0  
COMMIT TRANSACTION

但我得到了这个错误，

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'pkAR_Transactions'. Cannot insert duplicate key in object 'dbo.AR_Transactions'.
The statement has been terminated.

我尝试运行插入语句，一切正常，但这个查询失败了......而且 pkAR_Transactions 主键显然不是非增量，这就是为什么我正在做这个 hack 来获取最后一个条目在该字段中

这是我的两个表的数据库结构..

AR_Transactions 表

column name type allow null 
Trans_ID bigint Unchecked
DateTime datetime Unchecked
Cashier_ID nvarchar(50) Checked
CustNum nvarchar(12) Unchecked
Trans_Type nvarchar(2) Unchecked
Prev_Cust_Balance money Checked
Prev_Inv_Balance money Checked
Trans_Amount money Unchecked
Payment_Method nvarchar(4) Checked
Payment_Info nvarchar(20) Checked
Description nvarchar(38) Checked
Invoice_Number bigint Unchecked
Store_ID nvarchar(10) Unchecked
Dirty bit Unchecked
Station_ID nvarchar(5) Checked
Payment_Type smallint Checked

Customers 表

column name type allow null 
CustNum nvarchar(12) Unchecked
First_Name nvarchar(15) Checked
Last_Name nvarchar(15) Unchecked
Company nvarchar(30) Checked
Address_1 nvarchar(30) Checked
Address_2 nvarchar(30) Checked
City nvarchar(20) Checked
State nvarchar(12) Checked
Zip_Code nvarchar(10) Checked
Phone_1 nvarchar(15) Checked
Phone_2 nvarchar(15) Checked
CC_Type nvarchar(5) Checked
CC_Num nvarchar(50) Checked
CC_Exp nvarchar(8) Checked
Discount_Level nvarchar(1) Unchecked
Discount_Percent real Unchecked
Acct_Open_Date datetime Checked
Acct_Close_Date datetime Checked
Acct_Balance money Checked
Acct_Max_Balance money Checked
Bonus_Plan_Member bit Unchecked
Bonus_Points int Checked
Tax_Exempt bit Unchecked
Member_Exp datetime Checked
Dirty bit Unchecked
Phone_3 nvarchar(15) Checked
Phone_4 nvarchar(15) Checked
EMail nvarchar(50) Checked
County nvarchar(30) Checked
Def_SP nvarchar(10) Checked
CreateDate datetime Checked
Referral nvarchar(20) Checked
Birthday datetime Checked
Last_Birthday_Bonus datetime Checked
Last_Visit datetime Checked
Require_PONum bit Unchecked
Max_Charge_NumDays int Checked
Max_Charge_Amount money Checked
License_Num nvarchar(20) Checked
ID_Last_Checked datetime Checked
Next_Start_Date datetime Checked
Checking_AcctNum nvarchar(20) Checked
PrintNotes bit Unchecked
Loyalty_Plan_ID bigint Checked
Tax_Rate_ID int Checked
Bill_To_Name nvarchar(30) Checked
Contact_1 nvarchar(30) Checked
Contact_2 nvarchar(30) Checked
Terms nvarchar(15) Checked
Resale_Num nvarchar(15) Checked
Last_Coupon datetime Checked
Account_Type smallint Checked
ChargeAtCost bit Checked
Disabled bit Checked
ImagePath nvarchar(255) Checked
License_ExpDate datetime Checked
TaxID nvarchar(20) Checked

Answer 1

运行这个

SELECT  
    (SELECT MAX(Trans_ID ) + 1 
     FROM [cresql].[dbo].[AR_Transactions]), 
     DATEADD(MINUTE, -30, Getdate()), 
     1, 
     1001, 
     'C', 
     100199,
     CustNum,
     -Acct_Balance,
     Acct_Balance  
FROM  [cresql].[dbo].[Customer]  
WHERE Acct_Balance <> 0

我怀疑你会看到所有记录都有相同的 trans_id。此外，这种黑客行为总是会导致竞争条件问题。

所以真正的问题是为什么它不自动递增以及如何在不引起竞争条件的情况下获得该值？该表的其他条目如何获取该值？这是你无法更改的第三方软件吗？如果有任何可能的方法来解决这个问题，我会首先这样做。

既然你说你不能改变数据库，你仍然需要找出它如何保留新的transids，这样就没有其他人会尝试使用相同的。这很关键。通常，他们通过将 id 添加到单独的 ids 表中并输出该插入的结果以在其余查询中使用来保留 id - 如果您不使用相同的方法，则会破坏事物。在您的情况下，您需要获取与要插入的记录数相同数量的 id。获得 id 后，就可以进行插入和更新。

Answer 2

如果以下任何问题的答案为“是”，请不要继续。

• 当您尝试进行更新时，AR 表是否正在使用？
• 您正在制作系统上开发脚本吗？

如果以下任何问题的答案为“否”，请不要继续。

• 我正在开发系统，
• 我有经过验证的生产备份，以防出现任何问题。

我认为问题在于您当前用于计算 AR id 值的方法。 代码

(SELECT MAX(Trans_ID ) + 1 FROM [cresql].[dbo].[AR_Transactions])

当脚本运行时， 返回单个值。为每个余额非零的客户插入单一值。您可以选择为客户表创建一个游标，以检索余额不为零的 customer_id 值。在代码中使用客户值将行添加到 AR 表中，然后将客户表中的余额归零。去找下一个顾客。