unable to remove SQL syntax in UPDATE query

Question

I am updating table when i run this query it give an error:

 client.query('UPDATE Campaign SET ( Name, StartDate ) VALUES ( "' +req.body.Name+ '" , "' +req.body.StartDate+ '" ) WHERE idCampaign = ' +id , function(err, result) {
    if(err) {
        console.log("err found" + err);
    }
    else{
        console.log(result);
        res.send(result[0])
    }

});

 ER_PARSE_ERROR: 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 '( Name, StartDate ) VALUES ( "" , "" ) WHERE idCampaign = 89126b2d-c906-11e2-9cf'

I dont know where the error is

Claudio Redi · Accepted Answer · 2013-05-30T13:00:34.873

1

According the error description, idCampaign is a string and not a number so you need to use quotes. Try with this

 ... WHERE idCampaign = '" + id + "'"

EDIT

I totally missed that your UPDATE statement was all wrong, I just paid attention to the error message. @RedBaron is correct but you still have to use the quotes on id. Try with this

"UPDATE Campaign SET Name='" + req.body.Name + "', StartDate = '" + req.body.StartDate+ "' WHERE idCampaign = '" + id + "'"

edited May 30 '13 at 13:00

answered May 30 '13 at 12:27

Claudio Redi

67,454
15
130
155

i am writing this query client.query( 'UPDATE Campaign SET Name=' + req.body.Name + ', StartDate= ' +req.body.StartDate+ ' WHERE idCampaign = "' + id + '"', function(err, result) { if(err) { console.log("err found" + err); } else{ console.log(result); res.send(result[0]) } }); – ana May 30 '13 at 12:54

score 1 · Answer 2 · answered May 30 '13 at 12:29

1

That is not how an update statement is used in MySQL. Look at the Documentation

Broadly the query should be like

'UPDATE Campaign SET Name=' + req.body.Name +', StartDate ='+req.body.StartDate+ ' WHERE idCampaign = ' + id

answered May 30 '13 at 12:29

RedBaron

4,717
5
41
65

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 'WHERE idCampaign = 351adc56-c906-11e2-9cfa-74867a028324' at line 1 @RedBaron – ana May 30 '13 at 12:44
@ana I think you should print out the query as a string, before you pass it to `client.query`. It will help you to debug it better. As Claudio has pointed out in his answer, you are probably missing out quotes (Especially since date is supposed to be quoted) – RedBaron May 30 '13 at 14:54

unable to remove SQL syntax in UPDATE query

2 Answers2