update column to two based on condition-CodePudding

I am trying to modify ONE column, I want to set some rows as true the others convert them to false

update products set on_sale=False where status=1 and seller=test;
update products set on_sale=true Where price > 100 and status=1 and seller=test;

the above works, but I believe it can be done in 1 query, I.e something like this

\\ python syntax for the if condition
update prodcuts set on_sale=(True if price > 100 else False) WHERE status=1 and seller=test

CodePudding user response：

You could do a single update with the help of a CASE expression:

UPDATE products
SET on_sale = CASE WHEN price > 100 THEN True ELSE False END
WHERE status = 1 AND seller = test;