CODEWITHSUNDEEP
sqlms-access
user3220812
user3220812

Reputation: 197

Converting SQL query to work with Microsoft Access

I need to run the following SQL query in MS Access. Would someone be able to explain how to convert this into the correct language? 

    UPDATE osmm_topo.boundaryline SET style_description =
CASE    
    WHEN featurecode = 10136 THEN 'Parish Boundary'
    WHEN featurecode = 10131 THEN 'District Boundary'
    WHEN featurecode = 10128 THEN 'Electoral Boundary'
    WHEN featurecode = 10127 THEN 'County Boundary'
    WHEN featurecode = 10135 THEN 'Parliamentary Boundary'
    ELSE 'Unclassified' 
END,
style_code = 
CASE
    WHEN featurecode = 10136 THEN 1
    WHEN featurecode = 10131 THEN 2
    WHEN featurecode = 10128 THEN 3
    WHEN featurecode = 10127 THEN 4
    WHEN featurecode = 10135 THEN 5
    ELSE 99 
END;

Upvotes: 0

Views: 40

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

I think that tahe Switch function will be your friend in your scenario:

UPDATE osmm_topo.boundaryline SET style_description =
Switch(
  featurecode = 10136, 'Parish Boundary',
  featurecode = 10131, 'District Boundary',
  featurecode = 10128, 'Electoral Boundary',
  featurecode = 10127, 'County Boundary',
  featurecode = 10135, 'Parliamentary Boundary',
  True, 'Unclassified'),
style_code = 
Switch(
  featurecode = 10136, 1,
  featurecode = 10131, 2,
  featurecode = 10128, 3,
  featurecode = 10127, 4,
  featurecode = 10135, 5,
  True, 99);

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

You can try to use IIF

IIf ( expr , truepart , falsepart )

instead of CASE WHEN in ms-access

UPDATE osmm_topo.boundaryline SET style_description = IIF(featurecode = 10136, 'Parish Boundary',IIF(featurecode = 10131,'District Boundary',IIF(featurecode = 10128,'Electoral Boundary',IIF(featurecode = 10127,'County Boundary',IIF(featurecode = 10135,'Parliamentary Boundary','Unclassified'))))) ,
style_code = IIF(featurecode = 10136, 1 IIF(featurecode = 10131,2,IIF(featurecode = 10128,3,IIF(featurecode = 10127,4,IIF(featurecode = 10135,5,99)))))

Upvotes: 1

Related Questions