I like the JOIN syntax, it's clearer to me than subqueries:
UPDATE a LEFT JOIN b ON (a.ib=b.i)
SET i1 = j1,
i2 = if(isnull(b.i), j2 ,i2)
WHERE a.id = jid;
This will change the jid record: i1 to j1 always and i2 to j2 if no match exists, and to the same value if the match exists. You may want to index b.i (probably as a primary key) if there are a lot of records and you want performance.
Your additional calculated value:
(select count(t1.num) from T t1)
Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function
error
However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:
group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)
just results in an ORA-22818: subquery expressions not allowed here
error.
The only ways around this are to either convert your scalar subquery to an aggregate value like so:
max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)
or
(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)
or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:
select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
from (select sum(t3.num) sum from t t3) t2;
or precompute your scalar subquery so it can be used in the group by clause:
select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
from t t2
, (select count(*) cnt from t) t1
group by case t1.cnt when 0 then 1 else 0 end
Best Answer
The correct syntax is:
Other minor improvements:
CASE WHEN <boolean_expression> ...
In your case:
If the two other columns (
board_stage
andalight_stage
) are not nullable, you can replace the case expression with a more simple boolean expression (parentheses added only for clarity):