Syntax error in MySQL update query

Question

I have a database table with 13 fields, 9 of which are populated by user input from a form. Inserting a new row works fine, but I'm having a problem with updating an existing row. I asked a friend for his input, and he couldn't help out.

I'm required to update all user-adjustable fields since each adjustment can cover one to all fields, thus requiring a query with multiple SET statements. The update query is

$query = sprintf("UPDATE tour 
  SET StopNum=%d, SET ForwardLink=%d, SET BackLink=%d, SET LeftLink=%d, 
  SET RightLink=%d, SET Name= '%s', SET Lat=%f, SET Lon=%f, SET Heading=%d 
  WHERE FileName='%s'", $_POST['curStop'], $_POST['nextStop'], $_POST['prevStop'],
  $_POST['leftStop'], $_POST['rightStop'], $location, $latitude, $longitude, 
  $heading, $image);

Using the above query, I get the following printout in an example update:

UPDATE tour SET StopNum=0, SET ForwardLink=0, SET BackLink=0, SET LeftLink=0, SET RightLink=0, SET Name= 'Start', SET Lat=98.154000, SET Lon=-75.214000, SET Heading=100 WHERE FileName='../panos/photos/1-prefix_blended_fused.jpg'

According to the mysql error message, I need to check the manual for the right syntax to use near

'SET ForwardLink=0, SET BackLink=0, SET LeftLink=0, SET RightLink=0, SET Name= 'S' at line 1

The field 'Name' is defined as a nullable varchar(250) container, so 5 letters is more than enough room for storage.

That's a completely generic SQL error. It usually means that the error is in the text immediately BEFORE the part it quotes at you. In this case, the error is using SET before each variable instead of just before the first one. — octern
– octern, Commented May 31, 2012 at 19:32

user1403947 · Accepted Answer · 2012-05-31 19:30:07Z

4

The syntax should be:

UPDATE table SET column=1,column1=2,column2=3 WHERE filename='asdf'

answered May 31, 2012 at 19:30

user1403947

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

1 Comment

Jason Over a year ago

That was the problem. I came across a bad example and needed to adapt it.

Collectives™ on Stack Overflow

Syntax error in MySQL update query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related