Menu
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?
554
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.
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.
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.
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.
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'
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):
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.
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.
You can delete the temporary table again when you're finished.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
