Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Join data and Update Question

Status
Not open for further replies.

datamaan

Technical User
Sep 21, 2000
3
US
Using this information could I ask if anyone could show me what the script would be to get the correct results. I am trying to take information from two tables that have a common ID number. When they match to create a new record in a third table.

I have 3 tables:
Accountproduct
Contract
ContractItem

Main Fields in tables:
In Accountproduct we have AccountProductID, accountID, userfield10
In Contract we have AccountID, userfield1
In ContractItem we have ContractitemID, ContractID, AccountProductID

I imported Accountproduct and contract into database. I have a matching ID number that was imported into each table that match the product and contract together. In the AccountProduct the ID number was imported into "userfield10" and for the Contract it was imported into "userfield1"

I am trying to do a join to match the records of the ID number. Then when a match is made it adds a record to the ContractItem table. I also need to make a random number for the ContractItem field "contractitemID
 
This SQL should give you a starting point for inserting records into the CONTRACTITEM table.

The only bit I'm not sure about is your random number requirement, I'm assuming you mean a unique key for the table that's generated somehow. The example below assumes you have an oracle DB and makes use of something called a Sequence but there are similar things built into most DB's. Which DB are you using?

Insert Into CONTRACTITEM (ContractitemID, ContractID, AccountProductID)
[tab]Select CID_SEQ.NextVal, UserField1, ACCOUNTPRODUCT.AccountProductID
[tab][tab]From ACCOUNTPRODUCT, CONTRACT
[tab][tab]Where ACCOUNTPRODUCT.UserField10 = CONTRACT.UserField1;

Mike
michael.j.lacey@ntlworld.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top