Updating with correlated subqueries

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

Updating with correlated subqueries

Author

Topic

Azuki707
Starting Member

1 Post

Posted - 2012-09-30 : 06:21:43

I have a table call CaseTypeCode with 2 columns: typeCode and caseType. Assuming that the CaseTypeCode table has been populated with all possible case types and each case type has been assigned a type code, replace the column caseType in the Case table with a new column typeCode. Add the correct type into the existing rows in the Case table to refer to the corresponding typeCode given the caseType of the case.

I have created a new column caseType in Case table, but it's now filled with NULL. I need to fill them with the caseType from CaseTypeCode. A subquery like below won't work because the subquery result in multiple output. I think I need a correlated subquery.


update [Case]
set [Case].typeCode = 
	(select ctc.caseType
	from [CaseTypeCode] as ctc
	where ctc.caseType=[Case].caseType);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:52:40

[code]
UPDATE c
SET c.typeCode=cc.TypeCode
FROM Case c
INNER JOIN CaseTypeCode cc
ON cc.caseType = c.caseType
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources