Microsoft Access - 案例查询 [英] Microsoft Access - Case Query

问题描述

我刚刚尝试使用 MS Access 2007现在我想根据其他列值更新一列，在 MY SQL 中它成功运行此查询

I've just try using MS Access 2007 now I want to update a column based on other column value, in MY SQL it was successfull running this query

UPDATE HAI
SET REGION=(
CASE
  WHEN (NUMREG LIKE '%1') THEN 'BDG'
  WHEN (NUMREG LIKE '%2') THEN 'JKT'
  WHEN (NUMREG LIKE '%3') THEN 'KNG'
END);

此查询不会在 MS Access 中运行，存在错误查询表达式中的语法错误(缺少运算符).我该如何解决这个问题?

This query won't run in MS Access, there is error Sytax error (missing operator) in query expression. How can I solve this?

推荐答案

Access SQL 中没有 CASE ... WHEN.您可以改用切换功能.

There is no CASE ... WHEN in Access SQL. You can use the Switch Function instead.

UPDATE HAI
SET REGION = Switch(
    NUMREG Like '*1', 'BDG',
    NUMREG Like '*2', 'JKT',
    NUMREG Like '*3', 'KNG'
    );

该查询使用 Access 的默认值(ANSI 89 模式)* 而不是 % 通配符.如果要使用 % 通配符，可以使用 ALlike 比较运算符.

That query uses Access' default (ANSI 89 mode) * instead of % wildcard character. If you want to use the % wildcard, you can do it with the ALike comparison operator.

UPDATE HAI
SET REGION = Switch(
    NUMREG ALike '%1', 'BDG',
    NUMREG ALike '%2', 'JKT',
    NUMREG ALike '%3', 'KNG'
    );

这篇关于Microsoft Access - 案例查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！