Update Query - SQL Server Forums

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 Query

Author

Topic

Longy
Starting Member

5 Posts

Posted - 2009-11-30 : 05:32:00

I am trying to update a previously made table that contains the unique id of VisitId. This has been renamed from CallId when the table was made so when the table is updateed callid will equal visit id.

The problem I have is that the value I am updating can been changed multiple times by the user and is therefore in the table multiple times, I have got around this by pulling out the latest value using the max() function with a join on a subquery as can be seen in the code. this works and returns one value for each callid that has a value, I just can't for the life of me figure out how to get this to update the column.

SELECT dom.Value
FROM ClinicalSolutions.CallData_Fact call
inner join ClinicalSolutions.DataItemDefinition_Dim did on call.DataItemDefinitionId = did.DataItemDefinitionId
inner join ClinicalSolutions.DomainValue_Fact dom on dom.domainId = call.DomainId and dom.ValueId = call.valueId
INNER JOIN

(SELECT call1.Callid as 'Call1', did.DataItemDefinitionId, Max(call1.CallDataItemEnumeratedModifiedAt) as maxd
FROM ClinicalSolutions.CallData_Fact call1
INNER JOIN ClinicalSolutions.DataItemDefinition_Dim did on call1.DataItemDefinitionId = did.DataItemDefinitionId
WHERE call1.DataItemDefinitionId = '854'
GROUP BY call1.CallId, did.DataItemDefinitionId) as subquery

ON subquery.Call1 = call.CallId AND subquery.maxd = Call.CallDataItemEnumeratedModifiedAt
WHERE did.DataItemDefinitionId = '854'

Any help would be most appreciated.

Thank you

Longy

Longy
Starting Member

5 Posts

Posted - 2009-11-30 : 06:54:25

Resolved Sorry for any inconvenience and thankyou for all who have read this post and were trying for a solution it was very simple as below:

UPDATE DataSet_PL_W SET First_Rating_Entered = (
SELECT dom.Value
FROM ClinicalSolutions.CallData_Fact call
inner join ClinicalSolutions.DataItemDefinition_Dim did on call.DataItemDefinitionId = did.DataItemDefinitionId
inner join ClinicalSolutions.DomainValue_Fact dom on dom.domainId = call.DomainId and dom.ValueId = call.valueId
INNER JOIN

(SELECT call1.Callid as 'Call1', did.DataItemDefinitionId, Min(call1.CallDataItemEnumeratedModifiedAt) as maxd
FROM ClinicalSolutions.CallData_Fact call1
INNER JOIN ClinicalSolutions.DataItemDefinition_Dim did on call1.DataItemDefinitionId = did.DataItemDefinitionId
WHERE call1.DataItemDefinitionId = '854'
GROUP BY call1.CallId, did.DataItemDefinitionId) as subquery

ON subquery.Call1 = call.CallId AND subquery.maxd = Call.CallDataItemEnumeratedModifiedAt
WHERE did.DataItemDefinitionId = '854' AND Call.CallId = DataSet_PL_W.VisitId)

just confused me as it took over 5 minutes to run

Thank you again

Longy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-30 : 06:58:23

I would like to help but I am not able to understand...

Could you please give table structure as far as needed and sample data and wanted result in relation to sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-30 : 06:59:16

Oh - too late

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources