Search code examples
mysqlsqlsyntaxsql-updatetinyint

Update statement with TINYINT variables raises error in mySQL

I try to get a simple update query to work in mySQL, and I can't find an answer.

What I try to do, for "agroforestry" is a tinyint variable :

UPDATE Projects
SET agroforestry = FALSE,
WHERE project_id=2;

This following query works perfectly on the same dataset :

UPDATE Projects
SET contact_person_mail='[email protected]',
    geographical_scope='national',
    country='Belgium',
    budget=4500000,
    currency='EUR'
WHERE project_id=2;

... But I get a syntax error when I try to update tinyint (boolean) variables. I have tried all this :

agroforestry = FALSE
agroforestry = False
agroforestry = false
agroforestry = 'FALSE'
agroforestry = 'False'
agroforestry = 'false'
agroforestry = 0
agroforestry = 1
agroforestry = TRUE
...

Nothing works ! What am I doing wrong ?

Solution

  • In your example, you have

    UPDATE Projects
SET agroforestry = FALSE,
WHERE project_id=2;

    Take the comma out, after FALSE. It doesn't belong there. Commas in a SET clause exist only in separating columns. The last column (in this case, the only column) to be modified cannot be followed by a comma.