MySQL Update query not updating table

Question

Here is the table

and this is the MySQL Query -

UPDATE loan_accounts SET `Int_Rate` = TRUNCATE(`Int_Rate`*0.5/100+`Int_Rate`,2) WHERE `Loan_Amount`>400000;

Above query is not updating the table by increasing the interest rate by 0.5% for all the loans for which the loan amount is more than 400000.

Kindly help me in finding my mistake.

Data Type of Int_Rate is DECIMAL(7,2) I have to use this format only, due to this I used TRUNCATE function

What is the data type of Int_Rate? Maybe dividing it by 2 and then by 100 is resulting in a value of 0, and then you're just adding the original value back to that 0? — David
– David, Commented Dec 28, 2015 at 3:53
Interest rate for account #2 is 10.00. When you say you want the interest rate to increase by 0.5%, do you mean the new interest rate should be 10.50%? If so, update loan_accounts set int_rate = int_rate + 0.5 where loan_amount > 400000 should do it — zedfoxus
– zedfoxus, Commented Dec 28, 2015 at 4:15
First of all, if Int_Rate in your table is DECIMAL(7, 2) then your code should be working correctly - I tested it and it worked - for example, 12.50 changes to 12.56. But also, I'd say a simpler expression to update by 0.05% is to multiply by 1.005. — SeanN
– SeanN, Commented Dec 28, 2015 at 4:21
@zedfoxus Thank you your guess was correct and it shows me my silly mistake. — Vishal Deb
– Vishal Deb, Commented Dec 28, 2015 at 9:07
@VishalDeb - I have moved my comment to an answer. Feel free to wait for other answers before marking any of the answers as accepted and give closure to your question. That way, people searching for an answer don't have to scan through comments. — zedfoxus
– zedfoxus, Commented Dec 28, 2015 at 14:08

zedfoxus · Accepted Answer · 2015-12-28 14:06:53Z

2

If you are hoping to bump the interest rate from, say, 10.00 to 10.50, all you have to do is addition like this:

update loan_accounts 
set int_rate = int_rate + 0.5 
where loan_amount > 400000;

(Moved the information from comment to an answer so as to put closure to OP's question).

answered Dec 28, 2015 at 14:06

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1