A little trouble for a newbie
-
Everyone,
I have three queries.. 1st shows all the records that need updating.. 2nd shows the item that needs to be inserted, and third is the actual insertion..
My issue is that I can't seem to setup the flow to automatically go through the 1st query taking the value and inserting it into the 2nd and third queries where needed.. then move to the next record and so on..
Everything is being done in QA, I ahve done some ado.recordset programming in VB however I have never tried it in SQL does anyone have a suggestion on how I can run this rather than doing them one at a time.. Here are my scripts..
------------------------ CODE ---------------------------------------
Select Distinct U.SSB_BRANCH_ID
From Contact c Join C_CONTACT_EXT x on (c.ContactId = x.ContactId) Join C_FC_DATA u on(x.FCID = u.FCID)
Where c.accountId is NULL
Declare @Acct Nvarchar(64)
Set @Acct = (Select A.AccountId
From Account A Join C_Acount_Ext X on(A.AccountId = X.AccountId)
Where x.BranchId = '00171')
Update Contact
Set AccountId = @Acct
From Contact c Join C_CONTACT_EXT x on (c.ContactId = x.ContactId) Join C_FC_DATA u on(x.FCID = u.FCID)
Where c.accountId is NULL and U.SSB_BRANCH_ID = '00171'
____________________________________________________________
Thanks
-
Rich - could you post the DDLs of the tables ?!
**ASCII stupid question, get a stupid ANSI !!!** -
I was able to modify the script above to have it select the "Distinct Top 1" and then assign that to a variable and insert that into the other scripts..
I then ran through the entire set manually..
I would still like to know what the equal is to a loop in QA, either a for next or do...??
-
well - you can use a while loop with cursors but then you'll have the wrath of many at scc.com visited upon you! <:-)
If you post the DDLs you're (almost) guaranteed to get a response that will avoid loops!
**ASCII stupid question, get a stupid ANSI !!!** -
There's no wrath here
. But you might get a little rant or two 
. -
[ would still like to know what the equal is to a loop in QA, either a for next or do...??]
SQL is a set based language. While there are a varity of loops that can be used most of the time a set solution will give much better results. Your question is not unsual from someone who comes from a programing background as you do. Once they see the power of using SQL as it was intended they will never again consider using loops when the proglem can be handled on the servier side with a properly written SQL statement.
A set solution is where everything is done on one pass. Much Much faster than a loop. If You would post your DDL (table definitions) some test data and the expected results I feel sure that you will get your question answered. These guys are good but guessing at your table structure, the relationships between tables, the test data you are using and the expected results can lead to some confusing answers on their part. Posting the tables, and test data you are using makes it easy for everyone.
HTH
Mike
-
This is just taking a shot based on what you gave and without the DDL but give the following a try. If it doesn't work, we really need to see the DDL.
UPDATE dbo.Contact
SET AccountID = e.AccountID
FROM dbo.Contact a JOIN C_CONTACT_EXT b ON a.ContactID = b.ContactId
JOIN dbo.C_FC_DATA c ON b.FCID = c.FCID
JOIN dbo.C_Account_Ext d ON c.SSB_BRANCH_ID = d.BranchId
JOIN dbo.Account e ON d.AccountID = e.AccountID
WHERE a.AccountId IS NULL
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply