SQL Update Query help

Question

I have a table called Subject_table.. I'm trying to update a field in that table.... but i keep getting a syntax error.... Not sure wad i'm doing wrong. All fields in the table are of type VARCHAR(30)

This is what the queryString looks like

queryString2 = "update "+tablename+" set tittle='"+tittle+"' , desc='"+desc+"', creditPoints='"+creditPoints+"' where cid='"+cid+"'";

Actual query

UPDATE subject_table 
SET tittle='Subject 1', desc='Subject 1', creditPoints='5' 
WHERE cid='CSE11111';

I also have delete query which works fine...

Would appreciate the help..!!! The table

DROP TABLE IF EXISTS `dummy`.`subject_table`;
CREATE TABLE  `dummy`.`subject_table` (
  `cid` varchar(15) NOT NULL DEFAULT '',
  `tittle` varchar(45) NOT NULL DEFAULT '',
  `desc` varchar(550) NOT NULL DEFAULT '',
  `creditPoints` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Are you sure that `desc` is not recognized as a reserver word? Maybe I'm wrong... — Marco, Sep 06 '11 at 06:35
Could you please post the exact error message? And it is really called "tittle" with 2 "t"? — Ocaso Protal, Sep 06 '11 at 06:37
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 'desc='Project B', creditPoints='5' where cid='CSE3PRB'' at line 1 — John, Sep 06 '11 at 06:40
Exactly what I've already pointed out in my comment and answer: `desc` is a reserved word... :) — Marco, Sep 06 '11 at 06:42
In your delete query you use quoted _desc_, here it is why that's correct — Marco, Sep 06 '11 at 06:47
Thanks... I did this UPDATE subject_table s SET s.tittle='Subject 1', s.desc='Subject 1', s.creditPoints='5' WHERE s.cid='CSE11111'; — John, Sep 06 '11 at 06:49
possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — Ian Ringrose, May 06 '14 at 10:17

Marco · Accepted Answer · 2011-09-06T06:45:40.583

3

Are you sure that desc is not recognized as a reserved word?
Maybe I'm wrong...

Try this:

UPDATE subject_table 
SET tittle='Subject 1', `desc`='Subject 1', creditPoints='5' 
WHERE cid='CSE11111';

In your delete query you use quoted desc...

edited Sep 06 '11 at 06:45

answered Sep 06 '11 at 06:37

Marco

56,740
14
129
152

xdazz · Answer 2 · 2011-09-06T06:40:12.253

0

Should be where cid='CSE11111', and desc maybe the reserved keyword of your database, try to quote it by `.

edited Sep 06 '11 at 06:40

answered Sep 06 '11 at 06:33

xdazz

158,678
38
247
274

score 0 · Answer 3 · answered Sep 06 '11 at 06:38

0

"desc" is a keyword. Try putting that within square brackets like this:

update subject_table set tittle='Subject 1' , [desc]='Subject 1', creditPoints='5' where cid=('CSE11111');

answered Sep 06 '11 at 06:38

Mouli

1,621
15
20

Wrong quotes for MySQL (but we only just learned that this was a MySQL question), you'd want backticks for MySQL. – mu is too short Sep 06 '11 at 06:42

score 0 · Answer 4 · answered Sep 06 '11 at 06:38

The syntax error might be coming from the column name "desc".

Try escaping this column, as well as removing the brackets from the where clause.

update subject_table set tittle='Subject 1' , [desc]='Subject 1', creditPoints='5' where cid=('CSE11111');

SQL Update Query help

4 Answers4