-2

How to set the value of column tmp with mathematical operation ?

UPDATE mytable SET mycol = order / 100 + bar * 1000;

This command returns the following error message:

ERROR 1064 (42000): 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 'foo / 100 + bar * 1000)' at line 1

===

In the original question, my column name order was renamed foo.

DevonDahon
  • 7,460
  • 6
  • 69
  • 114
  • 1
    When asking your question, you're supposed to tell us what's wrong. What specific problem are you having with the statement you posted? – Ken White Apr 25 '19 at 02:15
  • @KenWhite I just updated my question, please, let me know if asking this way is correct. – DevonDahon Apr 25 '19 at 02:28
  • Your error message does not match your SQL. The error says `foo / 100 + bar * 1000)` (notice the closing `)`, where your SQL has `;` in that spot. So your problem code can't be your real code. Also, what are `foo` and `bar`? Are they column names? That information is not in your post. Neither is the data type of column `tmp`. We can't see the rest of your code or your table columns from where we are, so it's your job to provide those details in your question (along with your actual problem code). – Ken White Apr 25 '19 at 02:32
  • I just realized that I can't do: `UPDATE mytable SET mycol = foo` while this works `UPDATE mytable SET mycol = id`. `id` is `BIGINT(20)` and `mycol` and `foo` are `INT(11)`. What could be wrong in my table structure ? – DevonDahon Apr 25 '19 at 02:39
  • If you run this `UPDATE mytable SET mycol = foo` what message will you get? – FanoFN Apr 25 '19 at 02:42
  • @tcadidot0 `ERROR 1064 (42000): 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 'order' at line 1` – DevonDahon Apr 25 '19 at 02:43
  • That's strange, I can't replicate that error. I even try with your datatype but its not that. – FanoFN Apr 25 '19 at 02:50
  • 1
    @tcadidot0 It seems you deleted your previous message which gave me the hint to fix my problem, `foo` was actually `order` which is a **reserved word**, renaming the column to `sort` fixed my issue. Thanks a lot ! – DevonDahon Apr 25 '19 at 02:51
  • 1
    I see, so it's not my eyes were seeing things. I did saw `order` .. ok, glad I could help, I guess.. ;) – FanoFN Apr 25 '19 at 02:52
  • 1
    @tcadidot0 Fortunately, you saw my comment before I changed it and I saw your before you removed it ;-) If you rewrite your answer below, I'll set it as accepted answer. – DevonDahon Apr 25 '19 at 03:00
  • Possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) – Peter O. Apr 25 '19 at 12:25

2 Answers2

0

You write the query statement correctly on Mysql. It did not cause any error. The columns tmp, bar and foo need the same data type

Bon BN Le
  • 1
  • 3
0

You need to add bracket in your formula. This foo / 100 + bar * 1000 will return different value without bracket. For example:

-- lets say foo=5 and bar=6
5 / 100 + 6 * 1000 = 6000.0500
5 / (100 + 6) * 1000 = 47.1698
5 / ((100 + 6) * 1000) = 0.0000

-- if your formula look like this ((100 + bar)/foo) * 1000
((100 + 6)/5) * 1000 = 21200.0000

You have to make sure which of the mathematical formula you want to do first then enclose it with a bracket.

FanoFN
  • 6,815
  • 2
  • 13
  • 33