Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 554
user2520291 Avatar asked Jul 01 '13 14:07

user2520291


People also ask

Can you write update query with WHERE condition?

Learn Python + JavaScript + Microsoft SQL for Data science The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Can we use 2 conditions in WHERE clause?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.

How do I update two conditions in SQL?

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.

How do I change multiple values in an update statement?

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.


2 Answers

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'
like image 178
Gordon Linoff Avatar answered Oct 03 '22 01:10

Gordon Linoff


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.

like image 25
Christian Specht Avatar answered Oct 03 '22 01:10

Christian Specht