Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dcqster
Starting Member
4 Posts |
Posted - 2008-02-13 : 14:14:13
|
Any idea how I could do this efficiently? For example, the SiteName & SLAClass field using select statements each time may bog down the system.Also, I’d like to feed the CustID and Subject fields from another table call Profile instead of typing the CustID field each time. The result of this statement is to search for customers in the subject line and if customer is found then add the customer information into the Detail table. The Profile table contains all customer information. UPDATE [TEST3].[dbo].[Detail] SET [CustID] = 'Book Fairs' /*fill in with field from the Profile table automatically*/ ,[SiteName] = (SELECT distinct([Profile].[SiteName] ) FROM [TEST3].[dbo].[Profile], [TEST3].[dbo].[Detail] WHERE [Profile].[CustID] = [Detail].[CustID]) ,[SLAClass] = (SELECT distinct([Profile].[SLAClass]) FROM [TEST3].[dbo].[Profile], [TEST3].[dbo].[Detail] WHERE [Profile].[CustID] = [Detail].[CustID]) WHERE [Detail].[CallID] IN (SELECT [CallLog].[CallID] FROM [TEST3].[dbo].[CallLog], [TEST3].[dbo].[Subset], [TEST3].[dbo].[Asgnmnt] WHERE [CallLog].[CallType] = 'DREAM' AND [CallLog].[Subject] LIKE '%Book Fairs%' ) /*fill in with field from the Profile table automatically*/ |
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-13 : 14:27:32
|
I need some test data and expected results to REALLY give a good answer, but here is my first pass:Update DSet --Why are you filling the fields in like this from the Profile table?CustID = P.CustID,SiteName = P.SiteName,SLAClass = P.SLAClassFrom TEST3.dbo.Detail DInner Join TEST3.dbo.CallLog C On D.CallID = D.CallIDInner Join TEST3.dbo.Profile P On P.CustID = D.CustIDWhere C.CallType = 'DREAM'And C.Subject Like '%' + P.CustID + '%' |
|
|
dcqster
Starting Member
4 Posts |
Posted - 2008-02-13 : 14:57:22
|
Thanks for the quick response. I gave it a try but it affected 0 records. Also I changed thisInner Join HEAT_SUZ_3.dbo.CallLog C On D.CallID = D.CallIDto this but no luck:Inner Join HEAT_SUZ_3.dbo.CallLog C On C.CallID = D.CallIDYou asked: --Why are you filling the fields in like this from the Profile table?It is my goal to search for Customers Names in the Subject field and if the customer name is found then add the Customer information from the Profile table to the Detail table. The Profile table holds all my customers.The CallLog table holds my ticket information including the Subject of the request.The Detail table is a newly introduced form that is visible to my technicians holding various information including some of my Customer information.My way worked but, as you see, would require manual input of the Customer ID to the 2 locations. Find CustID in Detail.Subject and place Profile fields in the Detail equivalent fields.I hope this helps clarify. |
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-13 : 15:35:08
|
That clarifies a few things. Try this:Update DSet SiteName = P.SiteName,SLAClass = P.SLAClassFrom TEST3.dbo.Detail DInner Join TEST3.dbo.Profile P On P.CustID = D.CustIDInner Join TEST3.dbo.CallLog C On C.Subject Like '%' + D.CustID + '%'Where C.CallType = 'DREAM' |
|
|
dcqster
Starting Member
4 Posts |
Posted - 2008-02-13 : 16:15:29
|
Much closer! It's not pulling up any records yet and I assured that one Profile CustID definitely matches one of the words in a Subject field.I think the problem may be this:Inner Join HEAT_SUZ_3.dbo.Profile P On P.CustID = D.CustIDEven though the Profile & Detail tables have the CustID field, there are currently no values in the Detail form for CustID so it has nothing to join with.Here are the fields --Profile:CustID, SiteName, SLAClassCallLog:CallID, CallType, SubjectDetail:CallID (populated), CustID (new), SiteName (new), SLAClass (new) |
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-13 : 17:11:17
|
Ok, try the following:Update DSet CustID = P.CustIDSiteName = P.SiteName,SLAClass = P.SLAClassFrom TEST3.dbo.Detail DInner Join TEST3.dbo.CallLog C On C.CallID = D.CallIDInner Join TEST3.dbo.Profile P On C.Subject Like '%' + P.CustID + '%'Where C.CallType = 'DREAM' |
|
|
dcqster
Starting Member
4 Posts |
Posted - 2008-02-13 : 18:00:47
|
I have it:Update DSet CustID = P.CustID,SiteName = P.SiteName,SLAClass = P.SLAClassFrom TEST3.dbo.Detail D, TEST3.dbo.Profile P, TEST3.dbo.CallLog C Where C.CallID = D.CallID and P.CustType = 'Company' and C.CallType = 'DREAM' and C.Subject Like '%' + P.CustID + '%'I definitely over complicated things and you are so great for giving a newbie the opportunity to learn while dealing with things in a real life situation. Thanks so much for your help! |
|
|
|
|
|
|
|