Add numerical value to every numerical value of a column mysql

Question

I have a database with a rate column with numerical data. I would like to update the data in this column by adding 20 to each.

I am trying,

UPDATE 'refinance' SET 'rate' = rate + 20

I get a syntax error when I simulate this query :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''refinance' SET 'rate' = rate + 20' at line 1

Thanks for the input on this. Eric

There is no error in the query bro. So mention your error too. "table" is a keyword in mysql. Is your table_name "table"? — Farrukh Sarmad, Feb 01 '18 at 14:08
the error is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 — Zeusofolus, Feb 01 '18 at 14:10
you will get this error if you use the mysql reserved words etc:table,from etc.. — Ponni, Feb 01 '18 at 14:13
Welcome to SO. Please add any errors **in** your question so that it is complete as is. — Glubus, Feb 01 '18 at 14:18
Are you executing 2 queries at once? The error indicates that there is a problem right before your actual query or at the first character. — juergen d, Feb 01 '18 at 14:19
No I am just using this one update command. The column format is decimal(11,2) would that have any impact on this? — Zeusofolus, Feb 01 '18 at 14:33
https://stackoverflow.com/questions/2762851/increment-a-database-field-by-1 — Mawg says reinstate Monica, Feb 01 '18 at 14:34
Thanks for the suggestion @mawg but i do not want just one row so I don't want to use the where clause to target and id I want to update every value in every row of the column by a value of 20 — Zeusofolus, Feb 01 '18 at 14:38

Ponni · Answer 1 · 2018-02-01T14:16:41.170

1

Please refer the update table syntax, you need to specify the tablename not table keyword.

UPDATE refinance SET rate = rate + 20;

https://dev.mysql.com/doc/refman/5.7/en/update.html

https://www.w3schools.com/sql/sql_update.asp

edited Feb 01 '18 at 14:16

answered Feb 01 '18 at 14:06

Ponni

433
5
17

Yes I am using the actual table name in the query – Zeusofolus Feb 01 '18 at 14:16
1

then you please update the exact query which you have tried and error in the question – Ponni Feb 01 '18 at 14:18

score 0 · Answer 2 · answered Feb 01 '18 at 17:54

So it turns out this query works after all, I only get the error when I press the "Simulate Query" button in phpMyAdmin when I hit the "go" button it processes without error.

I am relatively new to sql so not sure why this would be the case or what the benefit of simulation is if it is not a representation of the actual syntax. Thanks to everyone for the input and time.

Eric

Add numerical value to every numerical value of a column mysql

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''refinance' SET 'rate' = rate + 20' at line 1

2 Answers2