使用Union Function创建更新查询？

I have an Excel table that I import to an Access 2010 database table. The only problem is the data for one field (Health Plan) is not consistent with what we currently use in our database. For example the data that is being imported shows "GA" vs what it should be, "CHC - GEORGIA". I'm able to make a simple Update query for changing ONE at a time, but I have 15+ states that need to be changed. This is what I was shooting for in combining these into one query:

我有一个Excel表,我导入到Access 2010数据库表。唯一的问题是一个字段(健康计划)的数据与我们目前在数据库中使用的数据不一致。例如,正在导入的数据显示“GA”与它应该是什么,“CHC - GEORGIA”。我能够一次更改一个简单的更新查询,但我有15个以上的状态需要更改。这是我在将这些组合成一个查询时所拍摄的内容:

UPDATE [Dan - CSF Quotes] SET [Dan - CSF Quotes].[Health Plan] = "HEALTHAMERICA And HEALTHASSURANCE"
WHERE ((([Dan - CSF Quotes].[Health Plan])="PA"))

UNION 

UPDATE [Dan - CSF Quotes] SET [Dan - CSF Quotes].[Health Plan] = "CHC - GEORGIA"
WHERE ((([Dan - CSF Quotes].[Health Plan])="GA"));

.
.
.
etc

Is there any easy way to change/update the values to this one field?

有没有简单的方法来更改/更新这个字段的值?

2 个解决方案

#1

You could use SWITCH to apply different conditions to a single update statement:

您可以使用SWITCH将不同的条件应用于单个更新语句:

UPDATE [Dan - CSF Quotes] 
SET [Health Plan] = SWITCH
                    (   [Health Plan] = "GA", "CHC - GEORGIA",
                        [Health Plan] = "PA", "HEALTHAMERICA And HEALTHASSURANCE"
                    )
WHERE [Health Plan] IN ("GA", "PA");

#2

Since you have 15+ search/replace pairs, consider storing them in a separate table such as the HealthPlanReplacements sample table below. Then you can use an UPDATE query which joins the replacements table to your primary data table.

由于您有15个以上的搜索/替换对,请考虑将它们存储在单独的表中,例如下面的HealthPlanReplacements示例表。然后,您可以使用UPDATE查询将替换表连接到主数据表。

This query does what I think you want:

此查询执行我认为您想要的内容:

UPDATE [Dan - CSF Quotes] AS dq
    INNER JOIN HealthPlanReplacements AS hpr
    ON dq.[Health Plan] = hpr.search_plan
SET dq.[Health Plan] = [hpr].[replace_plan];

And this is the HealthPlanReplacements table I used to test that query in Access 2007:

这是我用于在Access 2007中测试该查询的HealthPlanReplacements表:

search_plan replace_plan
----------- ---------------------------------
GA          CHC - GEORGIA
PA          HEALTHAMERICA And HEALTHASSURANCE

#1