Hi Garry, As far as i understand you need to update two tables at the same time, but instead you are doing the other way around i guess? You are first inserting into one table, then retrieving from that table to update the tblStock, why not update them at the same time, in the same code?
LOCK TABLES tblStock WRITE, tblLoans WRITE; INSERT INTO tblLoans .... (the details you mentioned are inserted here); UPDATE tblStock SET StockNumber=StockNumber-value (since you already know it replace the value relating to your programming syntax) WHERE ... ; UNLOCK TABLES; And while inserting and updating use LOCK TABLES to assure that no other thread reads or writes from your stock, and exit safely afterwards. I hope this helps. arda, > On Sun, 2003-02-16 at 16:38, Arda Balci wrote: > > Hi Garry, > > > > Check INSERT ... SELECT syntax from the manual, > > here is the example from there: > > > > INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE > > tblTemp1.fldOrder_ID > 100; > > > > If the source values are to be processed before inserting into the target > > table then you may use the mysql functions in your query. > > > Thanks but this does not solve my problem, here it is in full: > I have a stock control database where users can borrow stock. There are > three tables: tblStock, tblBorrowers and tblLoans. Table loans is a join > table which references the other two. When items of stock are borrowed, > the details are entered into tblLoans. In tblStock there is a > StockNumber attribute. I need to update this when stock is borrowed. My > thinking goes something like this: > 1/ find the newly entered record in tblLoans which contains the number > of items borrowed using either max(id) or last_insert_id, the id > attribute being auto-increment. > 2/ select the number of items borrowed for this id > 3/ update tblStock using this value - StockNumber=StockNumber-value > > i can only think that subselect queries will solve the problem. Although > i have accomplished it in three separate queries using user variables i > really need a way to do it in one query. Can anyone help or is there a > more elegant way to update stock levels in such a database without front > end programming. > > regards > garry > > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
