Update case problem - 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 case problem

Author

Topic

ptpakkan
Starting Member

2 Posts

Posted - 2009-07-23 : 09:59:38

Hello,

I would like to do an case query that checks two things. With the one in below i get an error message "Incorrect syntax near keyword CASE". I have limits for different continents in a different table. Does anyone know if this is even possible in sql?

UPDATE #cities
SET size =
CASE
WHEN #cities.continent = 'Europe' THEN
CASE
WHEN population between [small_low] and [small_high] THEN 'Small'
WHEN population between [medium_low] and [medium_high] THEN 'Medium'
WHEN population between [large_low] and [large_high] THEN 'Large'
ELSE NULL
FROM #cities, limits
WHERE limits.continent = 'Europe'
END
WHEN #cities.continent = 'Asia' THEN
CASE
WHEN population between [small_low] and [small_high] THEN 'Small'
WHEN population between [medium_low] and [medium_high] THEN 'Medium'
WHEN population between [large_low] and [large_high] THEN 'Large'
ELSE NULL
FROM #cities, limits
WHERE limits.continent = 'Asia'
END
END

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-23 : 10:06:38

awesome :)

1st you need to join table #cities and table #continents

and case syntax is not used the way you try
should be something like this:
UPDATE c
SET size = CASE WHEN population between [small_low] and [small_high] THEN 'small'
FROM #cities c
-- join hoes here
WHERE continent = 'Europe'

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-23 : 10:10:50

try this


UPDATE #cities SET size =

CASE #cities.continent
  WHEN 'Europe' THEN
    CASE WHEN limits.continent = 'Europe'
		CASE 
			WHEN population between [small_low] and [small_high] THEN 'Small'
			WHEN population between [medium_low] and [medium_high] THEN 'Medium'
			WHEN population between [large_low] and [large_high] THEN 'Large'
			ELSE NULL
		END
  END

WHEN 'Asia' THEN
 CASE WHEN limits.continent = 'Asia'
	CASE 
		WHEN population between [small_low] and [small_high] THEN 'Small'
		WHEN population between [medium_low] and [medium_high] THEN 'Medium'
		WHEN population between [large_low] and [large_high] THEN 'Large'
		ELSE NULL
    END
END
FROM #cities inner join limits on limits.continent=#cities.continent

ptpakkan
Starting Member

2 Posts

Posted - 2009-07-23 : 10:20:59

Did this the asgast way and it worked real nice! Thank you very very much guys :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources