Min Ko Ko Min Ko Ko - 3 months ago 14
MySQL Question

Update a column by subtracting a value between date

I substract the column value but it not working..

avaroom

5





UPDATE `roomcalendar`
SET avaroom = avaroom - 1 and
day BETWEEN '2016-08-31' and '2016-08-31' and roomname='Standard Room'


when i run this query. the avaroom value change to 1. The result be 4.Why?

Answer

You are supposed to use WHERE CLAUSE

UPDATE `roomcalendar` 
SET avaroom = avaroom - 1 
WHERE day BETWEEN '2016-08-31' and '2016-08-31' and roomname='Standard Room';

Simulating your problem:

SET @avaroom := 5;

SET @avaroom := @avaroom - 1 AND 2 BETWEEN 1 AND 3;

SELECT @avaroom; Output is 1

See Demo

Before setting the value to @avaroom variable the expression evaulates to a boolean value and in this case the expression always resolves into 1.