Update statement syntax ... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update statement syntax ...

Author

Topic

LaurieCox

158 Posts

Posted - 2010-09-01 : 16:20:15

I have these two tables


CREATE TABLE #TestData_1(
	PATID varchar(10) NULL,
	EpisodeNumber int NULL,
	DiagnosisType varchar(10) NULL)

INSERT INTO #TestData_1 (PATID,EpisodeNumber)
SELECT  '1018', 26  UNION ALL
SELECT  '32181', 4  UNION ALL
SELECT  '32181', 4  UNION ALL
SELECT  '32181', 4  UNION ALL
SELECT  '32181', 4  UNION ALL
SELECT  '43497', 1  UNION ALL
SELECT  '603', 38  UNION ALL
SELECT  '603', 49

CREATE TABLE #TestData_2(
	PATID varchar(10) NULL,
	EpisodeNumber int NULL)

INSERT INTO #TestData_2
SELECT  '603', 38  UNION ALL
SELECT  '603', 38  UNION ALL
SELECT  '603', 39  UNION ALL
SELECT  '603', 40  UNION ALL
SELECT  '603', 41  UNION ALL
SELECT  '603', 3  UNION ALL
SELECT  '43497', 1  UNION ALL
SELECT  '1018', 27  UNION ALL
SELECT  '1018', 29  UNION ALL
SELECT  '1018', 30  UNION ALL
SELECT  '1018', 31

I want to update the column DiagnosisType in #TestData_1 using the following rule:


If at least record exists in #TestData_2 for a given PATID/EpisodeNumber then 
   DiagnosisType = 'U' 
Else
   DiagnosisType = 'A'
End if

So I have come up with this query to find the correct diagnosisType:


SELECT d.patid,
       d.episodeNumber,
       case 
           when  h.patid is not null then 'U'
		   else 'A'
       end as diagnosisType
  FROM #TestData_1 d left join (select distinct
                                       x.patid,
                                       x.episodeNumber
                                  from #TestData_2 x) h
                           on d.patid = h.patid
                          and d.EpisodeNumber = h.EpisodeNumber

That gets me this output:


patid episodeNumber diagnosisType
===== ============= =============
1018  26            A
32181 4             A
32181 4             A
32181 4             A
32181 4             A
43497 1             U
603   38            U
603   49            A

Which is the correct output.

But how do I wrap it in an Update Statement?

I tried this:


update #TestData_1 set DiagnosisType = y.diagnosisType
 from  (SELECT 
           case 
			   when  h.patid is not null then 'U'
			   else 'A'
           end as diagnosisType
      FROM #TestData_1 d left join (select distinct
                                       x.patid,
                                       x.episodeNumber
                                  from #TestData_2 x) h
                           on d.patid = h.patid
                          and d.EpisodeNumber = h.EpisodeNumber) y

But this gives me this output:


patid episodeNumber diagnosisType
===== ============= =============
1018  26            A
32181 4             A
32181 4             A
32181 4             A
32181 4             A
43497 1             A
603   38            A
603   49            A

Which is wrong. The values in red should be U not A.

Thanks,

Laurie

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-01 : 16:26:52

Using your statement as a base:

UPDATE 
	d
SET 
	DiagnosisType =
           case 
			   when  h.patid is not null then 'U'
			   else 'A'
           end 
FROM 
	#TestData_1 d 
left join 
	(
		select distinct
		   x.patid,
		   x.episodeNumber
		from 
			#TestData_2 x
	)AS h
	on d.patid = h.patid
	and d.EpisodeNumber = h.EpisodeNumber

LaurieCox

158 Posts

Posted - 2010-09-01 : 16:35:59

Hi Lamprey,

That works! Thanks.

But one question, you said:

quote:
Originally posted by Lamprey

Using your statement as a base:

Were you implying that there might be a better base query to do the job?

I am here to learn. Just because I was able to get query that works, doesn't mean I came up with the best solution.

Thanks again,

Laurie

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-02 : 13:10:26

I don't know about better. However, there are several different ways to do this update. I persoanlly, like the LEFT OUTER JOIN method.

It might be more "proper" to do the SELECT DISTINCT in the derived table that you are joining to so that you do not update the DiagnosisType more than once for the same record.

For example you could just join to the table directly and skip doing the DISTINCT and it'll work just fine:

UPDATE 
	d
SET 
	DiagnosisType =
           case 
			   when  h.patid is not null then 'U'
			   else 'A'
           end 
FROM 
	#TestData_1 d 
left join 
	#TestData_2 h
	on d.patid = h.patid
	and d.EpisodeNumber = h.EpisodeNumber

But, if you run it, you'l notice that it updates 8 rows, not just 6. Because it is updating some rows more than once.

Alternatively, if you where to use the MERGE statement it'd fail because it doesn't allow the same row to be updated more than once. For example:

;WITH cteTestData2
AS
(
	SELECT *
	FROM #TestData_2
)
MERGE
	#TestData_1 AS Target
USING
	cteTestData2 AS Source
	ON Target.patid = Source.patid
	AND Target.EpisodeNumber = Source.EpisodeNumber
WHEN MATCHED THEN
	UPDATE SET DiagnosisType = 'U'
WHEN NOT MATCHED BY Source THEN
	UPDATE SET DiagnosisType = 'A'

Like I said there are lots of ways that it can be done. You could even update every row to "A". Then do an inner join to update the matched rows to "U". But, that, probably, wouldn't be as efficient. Again, there are lots of variables. :)

LaurieCox

158 Posts

Posted - 2010-09-03 : 11:06:00

Hi Lamprey,

Thanks for your response. You have given me more to think about and learn.

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources