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;

I hope you were able to find a solution. Remember, if the answer solved your issue, please mark it as accepted. — Morpheus, Sep 15 '15 at 20:47

Rahul · Accepted Answer · 2015-09-15T19:28:43.220

3

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])

edited Sep 15 '15 at 19:28

answered Sep 15 '15 at 19:23

Rahul

76,197
13
71
125

You just need `WHERE HRBI.[FY16StartingSalaryUSD] > 0` instead of `>=` – Morpheus Sep 15 '15 at 19:40
@dbagley, Op want `= 0 || <> 0`. To emulate that we need `>= 0` – Rahul Sep 15 '15 at 19:48
i believe OP wanted `=0 && <>0`. For the `=0`, OP should default the column `HRBI.[MktDISC%]` to zero and then use your solution to update the non-zero values. – Morpheus Sep 15 '15 at 19:53

score 0 · Answer 2 · answered May 13 '16 at 02:55

The Switch statement allows you to do exactly what you requested; when FY16StartingSalaryUSD=0, return a 0, otherwise return MarketMedianDISCUSD/FY16StartingSalaryUSD.

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

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

2 Answers2