具有多个WHERE条件的UPDATE语句 [英] UPDATE statement with multiple WHERE conditions

问题描述

我试图在Access中以不同的WHERE条件运行UPDATE查询:

I'm trying to run an UPDATE query in Access with different WHERE conditions:

UPDATE Table1
SET [Ticker] = "TXSFI" WHERE [Acct Numb] like "*03",
SET [Ticker] = "TESEI" WHERE [Acct Numb] like "*04";

为什么会出现错误?

推荐答案

您可以使用两个更新语句来做到这一点:

You can do this with two update statement:

UPDATE Table1
    SET Table1.[Ticker] = 'TXSFI' WHERE Table1.[Acct Numb] like '*03';

Update Table1
    SET Table1.[Ticker] = 'TESEI' WHERE Table1.[Acct Numb] like '*04';

或者，您可以使用条件更新(非Access版本)将它们组合在一起:

Or, you can combine these using conditional updates (non-Access version):

update table1
   SET Table1.[Ticker] = (case when  Table1.[Acct Numb] like '%03' then 'TXSFI'
                               when Table1.[Acct Numb] like '%04' then 'TESEI'
                          end)
   where Table1.[Acct Numb] like '%03' or Table1.[Acct Numb] like '%04'

顺便说一句，我猜您正在使用Access. SQL中的标准通配符为like '%03'，但是Access使用'*'而不是'%'.用您正在使用的数据库标记您的问题是一个好主意.

By the way, I am guessing that you are using Access. The standard wildcard in SQL would be like '%03' but Access uses a '*' instead of '%'. It is a good idea to tag your question with the database you are using.

话虽如此，您不能在Access中使用case:

Having said that, you can't use case in Access:

update table1
   SET Table1.[Ticker] = iif(Table1.[Acct Numb] like '*03', 'TXSFI', 'TESEI')
   where Table1.[Acct Numb] like '*03' or Table1.[Acct Numb] like '*04'

这篇关于具有多个WHERE条件的UPDATE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！