My question is how to improve an update. I had a set of records from an external source to add to a table. In the new data one of the key values was not known. It turns out that another key can be matched to key values in the old data (extracted to the table temp).
The relation between the keys can be seen by: select distinct products.prodid,products.groupid,temp.groupid as newgid from products join temp on left(products.prodid,6)=left(temp.prodid,6) where products.groupid=999; Where 999 was just an arbitrary value so I could enter the new data. The select showed the mapping of left(prodid,6) <--> groupid. In fact I had 49 values of groupid I could set in the new data. The update query I used: update products join temp on left(products.prodid,6)=left(temp.prodid,6) set products.groupid=temp.groupid where products.groupid=999; Clearly this query does not take advantage of the fact that left(prodid,6) --> groupid is a 1-to-1 mapping. It worked (after a while) because my data set was small. My question is how would you make such a query scalable to larger sets of records. I know I could use the table generated in the select. Is there a way in 4.0.x to do it in a single query. Can it be done with a subquery in 4.1? Lastly is there a link/book/whatever where I can read about this kind of stuff? Sorry this got kind of long. _____ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
