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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 UPDATE optimized

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 D
Set
--Why are you filling the fields in like this from the Profile table?
CustID = P.CustID,
SiteName = P.SiteName,
SLAClass = P.SLAClass

From TEST3.dbo.Detail D
Inner Join TEST3.dbo.CallLog C On D.CallID = D.CallID
Inner Join TEST3.dbo.Profile P On P.CustID = D.CustID
Where C.CallType = 'DREAM'
And C.Subject Like '%' + P.CustID + '%'
Go to Top of Page

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 this
Inner Join HEAT_SUZ_3.dbo.CallLog C On D.CallID = D.CallID
to this but no luck:
Inner Join HEAT_SUZ_3.dbo.CallLog C On C.CallID = D.CallID

You 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.



Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 15:35:08
That clarifies a few things. Try this:

Update D
Set
SiteName = P.SiteName,
SLAClass = P.SLAClass

From TEST3.dbo.Detail D
Inner Join TEST3.dbo.Profile P On P.CustID = D.CustID
Inner Join TEST3.dbo.CallLog C On C.Subject Like '%' + D.CustID + '%'
Where C.CallType = 'DREAM'
Go to Top of Page

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.CustID

Even 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, SLAClass
CallLog:
CallID, CallType, Subject
Detail:
CallID (populated), CustID (new), SiteName (new), SLAClass (new)

Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 17:11:17
Ok, try the following:

Update D
Set
CustID = P.CustID
SiteName = P.SiteName,
SLAClass = P.SLAClass

From TEST3.dbo.Detail D
Inner Join TEST3.dbo.CallLog C On C.CallID = D.CallID
Inner Join TEST3.dbo.Profile P On C.Subject Like '%' + P.CustID + '%'
Where C.CallType = 'DREAM'
Go to Top of Page

dcqster
Starting Member

4 Posts

Posted - 2008-02-13 : 18:00:47
I have it:

Update D
Set
CustID = P.CustID,
SiteName = P.SiteName,
SLAClass = P.SLAClass
From 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!
Go to Top of Page
   

- Advertisement -