LiveCode Forums.

Hi,

I struggle with the MySQL update syntax for the whole day but still couldn't find a solution. I hope some expert could help me with the UPDATE syntax.

put "personal_info" into tTableName
put "user_addr1", "user_addr2" into tFields

put field "fld_piUserID" into tuser_id

put field "fld_addr1" into taddr1
put field "fld_addr2" into taddr2

put "UPDATE " & tTableName & " SET (" & tFields & ") = VALUES(:1,:2) WHERE " & user_id &" =" & tuser_id & " "into tSQL
revExecuteSQL gConnectionID, tSQL, "taddr1", "taddr2"

I encounter the following error message. The contents in the error message are correct but it just did not update the table.
"There was a problem adding the records into the database:
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 '(user_addr1, useraddr2) = VALUES("10 Ceylon Road", "") WHERE user_id = 10008" at line 1"

By the way, user_id is set as an integer in the table.
Thanks for your help.

Change:
put "user_addr1", "user_addr2" into tFields
to:
put "user_addr1, user_addr2" into tFields

Marek

2 tips :

-it might be better to avoid syntax with parameters.

Instead of

put "UPDATE " & tTableName & " SET (" & tFields & ") = VALUES(:1,:2) WHERE " & user_id &" =" & tuser_id & " "into tSQL
revExecuteSQL gConnectionID, tSQL, "taddr1", "taddr2"

Prefer :

put "UPDATE " & tTableName & " SET user_addr1='"&taddr1&"',user_addr2='"&taddr2&"' WHERE user_id ='"& tuser_id &"'" into tSQL
revExecuteSQL gConnectionID, tSQL

-in order to debug your SQL queries, add an :

answer tSQL

Any "obvious" mistake will catch your eyes easily.

-Last point : in your original query, you might have another problem : WHERE " & user_id &" =" & tuser_id & " "into tSQL
Dont forget simple quotes for tuser_id (if the SQL column is a VARCHAR type for instance).

In most SQLs (including mySQL) you don't do a "SET (xx) = VALUES ()". You exclude the '=' if you are using the keyword 'VALUES'.

It's more correct to either do an
UPDATE xtable (field1, field2, field3) VALUES ('value1', 'value2', 'value3') WHERE ...
or
UPDATE xtable SET field1='value1', field2='value2', field3='value3' WHERE ...

It would be best to consult the mySQL manual for exact options available.

Cheers,
Dave

Thank you bangkok, you save my day. I got it right now. It is actually the ' ' that causes the problem.

bangkok wrote:2 tips :

-it might be better to avoid syntax with parameters.

Instead of

put "UPDATE " & tTableName & " SET (" & tFields & ") = VALUES(:1,:2) WHERE " & user_id &" =" & tuser_id & " "into tSQL
revExecuteSQL gConnectionID, tSQL, "taddr1", "taddr2"

Prefer :

put "UPDATE " & tTableName & " SET user_addr1='"&taddr1&"',user_addr2='"&taddr2&"' WHERE user_id ='"& tuser_id &"'" into tSQL
revExecuteSQL gConnectionID, tSQL

-in order to debug your SQL queries, add an :

answer tSQL

Any "obvious" mistake will catch your eyes easily.

-Last point : in your original query, you might have another problem : WHERE " & user_id &" =" & tuser_id & " "into tSQL
Dont forget simple quotes for tuser_id (if the SQL column is a VARCHAR type for instance).

Thanks all for your input and suggestions. Bangkok suggestions work for me.