Click here to Skip to main content
15,799,398 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have a book table with the columns ISBN and Inventory. After removing a row or thousands of rows from this table to avoid user error, I automatically calculate the Inventory of that book based on its ISBN.
I use the following code to do this, but it takes a long time when the user deletes hundreds of rows and if there are thousands of unique ISBNs.
string[] UniqueISBN = ISBN_Value.Distinct().ToArray();
OleDbCommand OleDbCommand_Update = new OleDbCommand();
OleDbCommand_Update.Connection = OleDbConnect;
for (int i = 0; i < UniqueISBN.Length; i++)
    OleDbCommand_Update.CommandText = string.Join(null, "Select Count(*) From BookTable Where ISBN = '", UniqueISBN[i], "'");
    OleDbCommand_Update.CommandText = string.Join(null, "Update BookTable Set Inventory = ", (int)OleDbCommand_Update.ExecuteScalar(), " Where ISBN = '", UniqueISBN[i], "'");

To answer this question, the following information may be required:

The array's length can range from 1 to 250 thousand, and each ISBN[i] has 13 digits and is stored in the database as a string type field.

In the following GIF image , the inventory of ISBN "0000000000000" is 3 and the inventory of ISBN "2222222222222" is 4.
Click to view
As you can see, when the two ISBNs "00000000000000" are removed, the inventory of the ISBN "00000000000000" changes automatically.
Unfortunately, when the number of unique ISBNs increases (for example, to 4,000 unique ISBNs), this solution becomes very slow.

I believe there is a quicker alternative, similar to the following code.
OleDbCommand_Update.CommandText = "Update BookTable Set Inventory = (Select Distinct Count(*) From BookTable Group By ISBN) Where ISBN IN (Select Distinct ISBN From BookTable Group By ISBN)";

I use the following tools:
.NET Framework 4.5.2, WPF, MS-Access 2007
If someone answers correctly, I will give them 5 stars.

What I have tried:

OleDbCommand_Update.CommandText = "Update BookTable Set Inventory = (Select Distinct Count(*) From BookTable Group By ISBN) Where ISBN IN (Select Distinct ISBN From BookTable Group By ISBN)";
Updated 1-Aug-22 23:12pm
OriginalGriff 1-Aug-22 12:06pm    
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?
Where are you stuck?
What help do you need?

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.
Reza jafery 1-Aug-22 17:14pm    
I've updated my question.
Gerry Schmitz 1-Aug-22 14:22pm    
You have no point of reference regarding performance. You should write a single "Access query" that recalcs "all" inventories. You might find that single brute force is faster that all the other variations. If not, you can at least tell if you're making any progress.
Reza jafery 1-Aug-22 17:17pm    
I've updated my question.
Richard Deeming 2-Aug-22 4:07am    

1 solution

Assuming you want to update the inventory for all remaining books, try something like this:
UPDATE BookTable SET Inventory = (SELECT Count(*) FROM BookTable As T WHERE T.ISBN = BookTable.ISBN)
Share this answer
Reza jafery 2-Aug-22 7:30am    
Thank you,
I tested your solution, I got this error:

System.Data.OleDb.OleDbException: 'Operation must use an updateable query.'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900