[Solved] Help with simple SQL statement

[JWB23]

When I attempt to execute this command:

update “Scent_Oil_Master” set “SO_Rand_Nbr” = RAND()

I get this error:

1: Unexpected token: “ in statement [update ]

which means nothing to me. I can't figure out what I'm doing wrong. Can anyone help me?

thanks,

Jim

[UnklDonald418]

It looks like you composed the SQL statement in Writer, which is an unfortunate choice. Writer uses different values for open and close quotation marks which are not understood by the HSQL database engine. The error message is complaining about the “ character/token.
Try

Code: Select all   Expand viewCollapse view

update "Scent_Oil_Master" set "SO_Rand_Nbr" = RAND()

Edit: Some further explanation. Writer uses the Unicode value (U+201C) for the left double quotation mark and (U+201D) for the right double quotation mark. HSQL expects the ASCII value (0x22) to enclose table and field names. Among other places the editor found at Tools>SQL uses the ASCII value. I often use the GUI found at Create Query in Design View to select all the fields I might need, then from the SQL view of the query, I copy and paste the field and table names into a Windows Notepad document where I compose the statement. Then I paste it into the editor at Tools>SQL. Also note, when entering a statement in Tools>SQL, any properly constructed field or table name enclosed in double quotes will appear in yellow. Any other color will likely result in an execution error.

[FJCC]

Are you executing this from the usual query pane, where you would enter a SELECT query, or from the menu Tools ->SQL. Only the latter will work.

[JWB23]

FJCC,

I'm using menu Tools-->SQL. any ideas?

- thx

[FJCC]

Are you using the proper double quote character as explained by UnklDonald418?

[Villeroy]

You can avoid typographic quoting in Writer with menu:Tools>Language>...>None

[UnklDonald418]

When I copy the SQL statement I presented earlier and paste it into Tools>SQL it executes as expected and adds a random number in "SO_Rand_Nbr" for each row in "Scent_Oil_Master"
According to the HSQLDB User Guide:
RAND() returns a random number x bigger or equal to 0.0 and smaller than 1.0
so be sure that "SO_Rand_Nbr" is a numeric type with at least one decimal place (I used 4 in my test).

[JWB23]

I tried it again just now and it worked fine.
I'm baffled.
???
oh well, all I care about is that it works!
- thanks all

[UnklDonald418]

Nothing terribly baffling, your problem was caused by using the wrong double quotes in the SQL statement.