MS Access如果条件只更新某些数据

问题描述：

我有一个查询来更新MS访问表True或False，几行也包含“-1”或“0”，现在，我需要一个查询更新表只为“-1”或“0”。请注意，我有15个字段在相同的表中更新与相似的数据（-1或0）。我无法给出标准，因为15个字段不允许标准查找或条件。以下是当前查询，由于数据量庞大，需要很长时间才能更新表格。MS Access如果条件只更新某些数据

UPDATE [2017 OpsData] 
SET [2017 OpsData].[FLG-ContProd] = 
    IIf([2017 OpsData].[FLG-ContProd]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-ContProd]="0","FALSE", 
    IIf([2017 OpsData].[FLG-ContProd]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-OutOfScope] = 
    IIf([2017 OpsData].[FLG-OutOfScope]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-OutOfScope]="0","FALSE", 
    IIf([2017 OpsData].[FLG-OutOfScope]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-Stabilize] = 
    IIf([2017 OpsData].[FLG-Stabilize]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-Stabilize]="0","FALSE", 
    IIf([2017 OpsData].[FLG-Stabilize]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-FastTrack] = 
    IIf([2017 OpsData].[FLG-FastTrack]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-FastTrack]="0","FALSE", 
    IIf([2017 OpsData].[FLG-FastTrack]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-FLR] = 
    IIf([2017 OpsData].[FLG-FLR]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-FLR]="0","FALSE", 
    IIf([2017 OpsData].[FLG-FLR]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-MetSLA] = 
    IIf([2017 OpsData].[FLG-MetSLA]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-MetSLA]="0","FALSE", 
    IIf([2017 OpsData].[FLG-MetSLA]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-ReOpen] = 
    IIf([2017 OpsData].[FLG-ReOpen]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-ReOpen]="0","FALSE", 
    IIf([2017 OpsData].[FLG-ReOpen]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-SpeakUp] = 
    IIf([2017 OpsData].[FLG-SpeakUp]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-SpeakUp]="0","FALSE", 
    IIf([2017 OpsData].[FLG-SpeakUp]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-HIPAA] = 
    IIf([2017 OpsData].[FLG-HIPAA]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-HIPAA]="0","FALSE", 
    IIf([2017 OpsData].[FLG-HIPAA]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-CSATBounce] = 
    IIf([2017 OpsData].[FLG-CSATBounce]="-1","TRUE", 
    IIf([2017 OpsData].[FLG-CSATBounce]="0","FALSE", 
    IIf([2017 OpsData].[FLG-CSATBounce]="TRUE","TRUE","FALSE"))), 
[2017 OpsData].[FLG-CA-ENGLISH] = 
     IIf([2017 OpsData].[FLG-CA-ENGLISH]="-1","TRUE", 
     IIf([2017 OpsData].[FLG-CA-ENGLISH]="0","FALSE", 
     IIf([2017 OpsData].[FLG-CA-ENGLISH]="TRUE","TRUE","FALSE")));

请尽量使问题更加微乎其微。请参阅[本帮助页面]（https://stackoverflow.com/help/mcve）。我不明白你的问题是什么。如果你想使用多个标准，你可以嵌套'IIF'语句。 –

所以这些字段都是文本类型，并且可以具有值“真”，“假”，“-1”，“0”。如果这是一次性修复，可以做查找/替换。也许应该将所有“True”和“False”条目更正为-1和0，并将该字段转换为yes/no类型。 – June7

答

字符串比较总是需要很长的时间。在这些字段上添加索引“可能有帮助”。

我会倾向于每栏做两遍。一，以纪念-1的为“TRUE”

UPDATE [2017 OpsData] 
SET [2017 OpsData].[FLG-ContProd] = "TRUE" 
where [2017 OpsData].[FLG-ContProd] = "-1";

然后一个标记一切“假”

UPDATE [2017 OpsData] 
SET [2017 OpsData].[FLG-ContProd] = "FALSE" 
where [2017 OpsData].[FLG-ContProd] <> "TRUE";

您可能还需要创建一个新列，这是一个真正的布尔（是的/否）并将其用于未来处理，这应该会更快。

UPDATE [2017 OpsData] 
SET [2017 OpsData].[FLG-ContProdNew] = False; 

UPDATE [2017 OpsData] 
SET [2017 OpsData].[FLG-ContProdNew] = True 
where [2017 OpsData].[FLG-ContProd] = "-1" 
or [2017 OpsData].[FLG-ContProd] = "Yes";

可以为每列执行一次传递。更新[2017 OpsData] SET [FLG-ContProd] = IIf（[FLG-ContProd] =“ - 1”，“TRUE”，“FALSE”）IsNumeric（[FLG-ContProd]）' – June7

谢谢..我得到了答案..这有助于很多 –

答

大概不会快，但更简单的结构：

UPDATE [2017 OpsData] SET [FLG-ContProd] = IIf([FLG-ContProd]="-1" Or [FLG-ContProd]="TRUE", "TRUE", "FALSE"), [FLG-OutOfScope] = IIf([FLG-OutOfScope]="-1" Or [FLG-OutOfScope]="TRUE", "TRUE", "FALSE"), [FLG-Stabilize] = IIf([FLG-Stabilize]="-1" Or [FLG-Stabilize]="TRUE", "TRUE", "FALSE"), [FLG-FastTrack] = IIf([FLG-FastTrack]="-1" Or [FLG-FastTrack]="TRUE", "TRUE", "FALSE"), [FLG-FLR] = IIf([FLG-FLR]="-1" Or [FLG-FLR]="TRUE", "TRUE", "FALSE"), [FLG-MetSLA] = IIf([FLG-MetSLA]="-1" Or [FLG-MetSLA]="TRUE", "TRUE", "FALSE"), [FLG-ReOpen] = IIf([FLG-ReOpen]="-1" Or [FLG-ReOpen]="TRUE", "TRUE", "FALSE"), [FLG-SpeakUp] = IIf([FLG-SpeakUp]="-1" Or [FLG-SpeakUp]="TRUE", "TRUE", "FALSE"), [FLG-HIPAA] = IIf([FLG-HIPAA]="-1"," Or [FLG-HIPAA]="TRUE", "TRUE", "FALSE"), [FLG-CSATBounce] = IIf([FLG-CSATBounce]="-1" Or [FLG-CSATBounce]="TRUE", "TRUE", "FALSE"), [FLG-CA-ENGLISH] = IIf([FLG-CA-ENGLISH]="-1" Or [FLG-CA-ENGLISH]="TRUE", "TRUE", "FALSE");

MS Access如果条件只更新某些数据

相关推荐