Help with newbie SQL syntax error Column not found

English
#1

I have an HSQLDB database in LibreOffice. It has a table name Clients. I just added a new column to the table named Delivery_Days. It is Text [VARCHAR] with a maximum length of 5 and a default value of MTWTF. It is not the key. I want to set it in all of the existing rows to the literal text “MTWTF” (without the quotes).

I attempt to execute the SQL statement UPDATE “Clients” SET “Delivery_Days” = “MTWTF” and get the error 1: Column not found: MTWTF

I have tried various variations of the DQL statement such as with and without the various quotes.

Question 1: What the heck am I doing wrong? How do I make the value MTWTF be taken as a text literal, not the name of a column?

And question 2, is there definitive documentation for HSQLDB SQL somewhere that would answer this question?

Thanks for your patience with an incredibly naive question.

Charles

#2

Try

UPDATE "Clients" SET "Delivery_Days" = 'MTWTF'

Tables and columns in double quotes, text content in single quotes.
“Delivery_Days” should be a varchar-field. When I see the name I thought it should be something like integer…

#3

Of course that works! Thanks.

Is this sort of thing documented anywhere?

I don’t understand your last sentence. It IS varchar.

#4

Documentation see: Base Guide 7.3

I thought days will be counted, not named like you do. For counting a numeric field is the right field.

#5

Thanks. I do see some examples that use single quotes but I don’t find any formal specification or rule.

Delivery_Days is either of two values, MTWTF or MWF. We currently only offer five-day-per-week deliveries. We are starting to offer the option of Monday-Wednesday-Friday deliveries. I did it this way rather than as a Boolean because I don’t know whether we might offer a third or fourth option in the future.

Thanks again for your help and patience.

#6

Embedded HSQL 1.8 of 2006: Chapter 9. SQL Syntax

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.