CODEWITHSUNDEEP
mysqlphpmyadmin
JoJo
JoJo

Reputation: 1473

Mathematical Operations in a SQL UPDATE query using MySQL

I am trying to add some amount to existing values in MySQL with this way but its not working for me, instead it is changing all values to zero.

UPDATE `fares` 
SET `fare`='`fare` + 70' 
WHERE `season_start`='2015-08-22' 
    AND `season_end`='2015-12-09' 
    AND  `ticket_class`='Y/Y' 
    AND `fare_type`='return' 
    AND `deptsub_code`='LHR' 
    AND `dest_code`='ASM'

I want to add 70 to all values. My column type is smallint(5)

Please suggest any other way or let me know where the error is?

Upvotes: 1

Views: 5020

Answers (3)

JoJo
JoJo

Reputation: 1473

I just done it with this way and its working for me:

UPDATE `fares` SET `fare`=(`fare` + 70) WHERE `season_start`='2015-08-22' AND `season_end`='2015-12-09' AND  `ticket_class`='Y/Y' AND `fare_type`='return' AND `deptsub_code`='LHR'

Anyway Thanks

Upvotes: 0

Pravin
Pravin

Reputation: 1147

 UPDATE `fares` SET `fare`=`fare` + 70 WHERE `season_start`='2015-08-22' AND `season_end`='2015-12-09' AND  `ticket_class`='Y/Y' AND `fare_type`='return' AND `deptsub_code`='LHR' AND `dest_code`='ASM'

Upvotes: 0

Sonic
Sonic

Reputation: 186

Try removing the apostrophes in the SET expression i.e.

UPDATE `fares` SET `fare`=`fare` + 70

rather than 

UPDATE `fares` SET `fare`='`fare` + 70'

Upvotes: 2

Related Questions