What is this value in your code: ID

Do you know which rows are being updated in your first table? If you don't, thats a problem. If you do not have some way to identifiy which values in your source table have been updated and do not have a way to grab the user_id associated with those rows, there is no way you can update your second table.

If, however, you know which rows have been changed and can grab the user_id you can do something like this:

UPDATE [SecondTable] set quantity = [quantity] WHERE user_id in(SELECT user_id FROM [sourceTable] WHERE [some condition that will return the recently updated rows])

This will return you all of the rows that have been updated in your source table and make the change in the Second table.


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Thanks for your time "dparsons"!

I am not sure what to use here:
WHERE [some condition that will return the recently updated rows]

Hi,

I've been trying to follow all of this. Maybe its just me but it doesn't seem like this has to be all that complicated. It looks like it would be easier to change design than to patch something that doesn't work well in the first place. Its almost like there are two carts, if I'm understanding this correctly. I don't see any advantage in that.

Richard

Richard,
   I agree that this seems like poor design, however, I will refrain from my opinion since I am not aware of the OP's entire situation.

melkin
   I am going to make the assumption that you are a beginner in all of this (at least as far as the database end is concerned anyway) so let me give you this little graphic:

-----------------------------------
| TABLE oddlistorders |
-----------------------------------
|user_id||quantity|||||||||||||||||
-----------------------------------
| 1 || 5 ||||||||||||||||||||
-----------------------------------
| 2 || 8 ||||||||||||||||||||
-----------------------------------
| 3 || 10 ||||||||||||||||||||
-----------------------------------

-----------------------------------
| TABLE oddlistprocessed |
-----------------------------------
|user_id||quantity|||||||||||||||||
-----------------------------------
| 1 || 3 ||||||||||||||||||||
-----------------------------------
| 2 || 6 ||||||||||||||||||||
-----------------------------------
| 3 || 4 ||||||||||||||||||||
-----------------------------------

Ok. Given these 2 tables if I wanted the quantity and user_id in the table oddlistprocessed to match the quantity and user_id in the oddlistorder table your query would be something like:

UPDATE oddlistprocessed
    SET oddlistprocessed.quantity = oddlistorders.quantity
        FROM oddlistorders, oddlistprocessed
            WHERE oddlistprocessed.user_id = oddlistorders.user_id

Here is the thing: if you were to execute this query, it is going to update every quantity in your oddlistprocessed table to match that of oddlistorders. What I find weird about your requirement is that you are replicating data which seem unnecessary.

Again, I want to point out to you, in your original post your query was: "Update oddlist Set quantity='"& quantity & "' WHERE user_ID=" & ID and that query, by design, is only going to update 1 record (since user_id is unique) unless id contains a string of multiple values.

This does seem redundant and not very normalized (as Richard alluded to) but if I have understood you correctly the above solution should work for you.

hth.


================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

You are right, I am a beginner....
Here is my strategy.... being that you are the pro, maybe you can give me a better direction....

My factory will update a database that sells products. My customers login and see a list of the products my factory loaded into the "oddlist" database.

The customer than can click and select different products. By selecting the different products, I send those products to a database that I use a sa temporary database to hold the produtcs the customer just selected. when they checkout, the temporay database called "oddlistorders" will go to a "ordersproccessed" database. all that works fine. The delemma was how to subtract the quantity from the first database called "oddlist". Otherwise that quantity will never be updated on the fly.

so I figured, if I can match the user_ID fields, I can change those particular user_IDs and update that quantity.

I dont know how else to hold the data when a user selcets different products.... Do i use cookies????

Much. much appreciated here for all your input.

Im no pro, just a jack of all trades, master of none ;] Ok, so you are pretty much working with an E-Comm site and inventory control/tracking system. Simple enough.

First, yes, you could store all of your cart information in a cookie but I don't recommend this as people are starting to disable cookies in their browser all together.

You could store the cart in session but, if the session times out, the cart will be lost.

In any case, maintaining a cart is a little outside the scope of this thread; if you want more information on that either start a new thread or send me an email via my profile.

Back to your problem. I am going to assume that user_id is some unique value that you use to identify a particular product in your system and not a particular user.

Now here is where you have a bit of a quandary: the way I have done this in the past, I am able to determine which products have been purchased when a user "checks out" based upon a unique cart id that links the shopping cart to the user.

So, in your example, if you had a unique value for the user, lets call it foo, when they checked out you would do something like this:

//move the shopping cart to your processed orders table
INSERT INTO oddlistprocessed(user_id, cartID, qunatity)
SELECT user_id, cartID, quantity from oddlistorders where cartID = 'foo'

//Update your inventory based upon what is in the shopping cart
UPDATE oddlist
    SET oddlist.quantity = (oddlist.quantity -oddlistprocessed.quantity)
        FROM oddlist, oddlistprocessed
            WHERE oddlist.user_id in (SELECT user_id from oddlistprocessed WHERE cartID = 'foo') and oddlistprocessed.CartID = 'foo'


The first query just moves rows from your cart table to your processed table, simple enough.

The second query updates the quantity in the oddlist table and subtracts what the user has ordered. This is how you know which rows to pull:

the query goes and looks at the processed table and finds all of the rows that contain the unique value 'foo'; from those rows it selects the unique value user_id from each of the rows (this constructs your IN() statement in the where clause) those IDs that are returned are then the IDs that are updated in the oddlist table.

Thats a very long explination and I hope you can understand what I am trying to say, but if you have any questions just post them back here!

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Good explanation Doug. It was just long enough. Oh my God, they're selling people!

LMFAO. Thats funny. ;]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Glad you liked that Peter. I can tell you've been following the thread!

Thanks for all that give to this forum. Very much appreciated.

Richard

dparsons, I Absolutely know what you are talking about and understand the sql statements! I would say I am an advanced beginner. THANK YOU SOOOOO MUCH for this info. I will give this a shot!