UPDATE statement with multiple WHERE conditions

问题:

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";

Why do I get an error?

回答1:

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';

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'

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.

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'

回答2:

It's possible to do this with one single query (without nesting IIFs), no matter how many different WHERE clauses you have.

This is similar to what I described in my answer here (second part):

1. Create a temporary table which looks like this:

   Acct Numb      NewTicker
   -------------------------
   *03            TXSFI
   *04            TESEI
   

   You can enter as many new rows as you want, each one with a "filter value" for the account number and a new Ticker value.

2. Update all values in Table1 with this single query:

   UPDATE Table1
   INNER JOIN tmp ON Table1.[Acct Numb] LIKE tmp.[Acct Numb]
   SET Table1.Ticker = tmp.NewTicker;
   

   Yes, the JOIN part looks strange at first glance, but it's actually possible to join with LIKE.

3. You can delete the temporary table again when you're finished.