Send a complete table description. (table name, attribute list with short field descriptor) entity relationships would be valuable.
Are you using a database (SQL Server, Oracle, etc) or TAB files? My understanding is intercepts.rocktype was added to the intercepts table and do not have any values? You want to populate the intercepts.rocktype based on the relationship of "from" and "to" in the down_hole_rocktype table. This does not intuitively sound like the right thing to be doing, why should there be any matches, except by coincidence. In any event the relation is and this is the where phrase to include in an update statement: "WHERE down_hole_rocktype.from = intercepts.from AND down_hole_rocktype.to = intercepts.to". You are presuming that if the from and to fields are the same then it is the same "hole"? This sounds very questionable, what degree of confidence do you have in this relationship? What about 2 or more holes that have the same "from" and "to" values. The last update statement executed will determine the values. I would suggest you DO NOT use this logic to populate rocktype, I am afraid it will lead to a false results that are not traceable. You might however use this relation to update the HOLE_ID, which you do not have. You can then perform other Q/A tests on the HOLE_ID relations to inspect for accuracy. This is still loaded with problems. The bottom line (no pun intended) is that you need to relate each intercept record with its associated down_hole_rocktype record. There may also be a valid 1:M relation here? Other thoughts: It sounds like you may need to update the "rocktype" lookup table, or make the necessary corrections to the rocktypes contained in the "Intercepts" table. Select all UNIQUE rocktype values from the intercepts table. This will give you list of values you need to add to the Rocktype lookup table or corrections that need to be made to the Intercept table. For example: If the intercepts table contains a rocktype of "QUART" which should always be "QUARTS" you can make the correction with a single SQL statement. UPDATE intercepts set rocktype = "QUARTS" where rocktype "QUART"; ATTRIBUTE naming: "From" is an SQL reserved word. It should not be used as a table or attribute name. You have just entered the quoting twilight zone. "To" while not reserved is very ambiguous. Rename these attribute to something meaningfull like: "from_depth", "f_depth" or "top_depth" -----Original Message----- From: Steve King [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 06:30 PM To: [EMAIL PROTECTED] Subject: MI-L Updating columns with multiple join. Hi There Could anyone please help me with the following problem. I have a geological drill hole database with a table containing down hole rocktype. with fields Hole ID, From, to and rocktype. I have a second table that I have generated from a gold assay table of intercepts which only contains the high grade intercepts. I have added a rocktype field to this intercepts table and I want to update this field with the appropriate rocktype code from the rocktype table. The problem is that the from and to fields in the assay table can be different to the from and to of the rocktype table but are often the same. What I would like to do is update those records which exactly match in the Hole ID, From and to fields and then I'll have to go through and manually complete the rest. But how do I do the update column join on the three field matches I want (there is no unique identifier for the records). I want to retain my assay intercept table as it is - I can't see how I can use SQL select and achieve this. Hope that's not too confusing. Any ideas Cheers Steve King --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 7933
