UPDATE statement with multiple WHERE conditions

ms-access, sql, t-sql

Solution

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'

Problem

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?

Original source