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
