table.columnname problem

lajkonik86

Found something i don't understand

UPDATE comment SET comment.REPLIES = comment.REPLIES+1 WHERE comment.THEORDER IN
(1.0101,1.01,1)

doesn't work because of the comment.REPLIES (REPLIES alone works perfectly)

using an sqlite database

Any ideas?

jimbovsat

in some SQL implementations, "comment" is a reserved word.

lajkonik86

that explains 🙂, so how would i get around that?

NogDog

You need to use one of the identifier quoting methods dicussed here, e.g.:

UPDATE 'comment' SET [comment].REPLIES = "comment".REPLIES + 1

(I've used all three methods just to add some additional confusion to the whole matter. 🙂 )

lajkonik86

UPDATE 'comment' SET 'comment'.REPLIES = 'comment'.REPLIES + 1

Error : SQL logic error or missing database
near ".": syntax error

jimbovsat

Well, the concept of RESERVED WORDS is that you DON'T use them.

As a DB designer, I start with that approach (I work with large teams and can't afford to have new contract team members learn incrementally). I don't know what flexibility you have, but it may be easier to rename this table and fix the occurences of the table name in the code?

lajkonik86

is there any list of reserved words for all the most common databases?

jimbovsat

Hmm. Didn't see a compendium; I've been in this biz a long time and learn something new every week (!) Gander these links:

http://developer.mimer.com/validator/sql-reserved-words.tml
http://msdn2.microsoft.com/en-us/library/aa238507(SQL.80).aspx
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_reserved
http://search.cpan.org/~chansen/SQL-ReservedWords-0.7/lib/SQL/ReservedWords/SQLite.pm

That last one looked interesting for your specific case, but you have to write a little snippet to extract the relevant info.

lajkonik86

good, solved, no more plain english in my database names 🙂

NogDog

The list of SQLite keywords is in the bottom part of this page. Note that "comment" is not one of them. 😕