Can you use two SET and WHERE Statements instead of ELSE in Access SQL?

Question

I am trying to use two SET and WHERE Statements in place of an ELSE Statement. Is that possible? I am getting a syntax error on the following:

UPDATE HRBI 
SET HRBI.[MktDISC%] = 0, HRBI.[MktDISC%] = (HRBI.[MarketMedianDISCUSD] / HRBI.[FY16StartingSalaryUSD])
WHERE HRBI.[FY16StartingSalaryUSD] = 0, HRBI.[FY16StartingSalaryUSD] <> 0;

Answer 1

First of all your WHERE statement syntax is wrong, you need to use AND or OR conditional operator. Second, did you looked at WHERE condition; it's impossible

WHERE HRBI.[FY16StartingSalaryUSD] = 0, HRBI.[FY16StartingSalaryUSD] <> 0;

It's like saying WHERE col1 = 0 and col1 <> 0 which is never going to be possible cause at any point in time the field can have either one of the value but not both. You can rather say >= 0 or use a OR condition

WHERE HRBI.[FY16StartingSalaryUSD] >= 0

You are doing the same mistake in SET clause as well. Your posted query SET clause can simplified to

SET HRBI.[MktDISC%] = (HRBI.[MarketMedianDISCUSD] / HRBI.[FY16StartingSalaryUSD])