StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I make this query work?
text
Body
copied!<p>I posted this <a href="https://stackoverflow.com/questions/6101484/looping-over-a-recordset-in-sql">question</a> yesterday and for some reason i cant get it to work. I got many good answers but they dont meet the scope of what i need. Basically i need to run a query that will loop through all the customers and grab their Acct_Balance and set it to 0 but before I set it to 0 i need to add an entry with that negative amount to the AR_Transactions table. So for real numbers sake if a customer has -200 in the Acct_Balance field in the customer table then i need to add an entry or 200 to bring the value to 0. Kind of like a line item to 0 it out. And vice verca for if the customer has 200 in the in the Acct_Balance field in the customer table then i need to add an entry or -200 to zero that out as well. I tried a few things. Here is one of the examples i tried. </p> <pre><code>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 </code></pre> <p>but i got this error</p> <pre><code>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. </code></pre> <p>I tried to run the insert statement and all works fine but with this query it fails....Also the pkAR_Transactions primary key is apparantly not non-increment which is why i am doing this hack to get the last entry in that field</p> <p>Here is my db structure for the two tables..</p> <p>AR_Transactions table</p> <pre><code>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 </code></pre> <p>Customers Table</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload