Basic use of SQL Update

[MSPhobe]

I have a simple table called xact
It has a field called frm

From the main window of the database, I selected “Tools/SQL…” which opened the Execute SQL Command dialog.

I am using the “standard” embedded Firebird engine.

I gave it…

SELECT * FROM xact WHERE frm IS NULL

… and all was well… I got a set of records.

I gave it…

UPDATE xact SET frm = ‘st19c31’ WHERE frm IS NULL

… and only got…

firebird_sdbc error:

*Dynamic SQL Error

*SQL error code = -204

*Table unknown

*XACT

*At line 1, column 13 caused by ‘isc_dsql_prepare’

… and so I turn to you, wonderful LibreOffice community!.. Tom (LO 7.0.3.1 (x64) / Windows 10)

[MSPhobe]

Turns out Firebird’s SQL is fussy about punctuation, which is of course normal and good. What it wanted was…

UPDATE "xact" SET "frm" = 'st19c31' WHERE "frm" IS NULL

Annoying, though, that it is inconsistent. The SQL SELECT given at the top works fine. I’m pleased to report, though, that at least the equivalent, using the “update punctuation” also works.

N.B…

WHERE "frm" IS NULL

or
WHERE “frm”=‘sie’

(Use the “IS” form JUST if you are selecting on “nullness”. (or saying WHERE “frm” IS NOT NULL.)

Oh… and on the subject of NULL? See…

https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/nullguide/firebird-null-guide.html

… which says “Few things in SQL lead to more confusion than NULL… In SQL, NULL is NOT a value. It is a state indicating that an item’s value is unknown or nonexistent. It is not zero or blank or an “empty string” and it does not behave like any of these values. [but it needn’t be if you] stick to the following simple definition: NULL means unknown.”

[Ratslinger]

Hello,

There is no inconsistency between the Query and SQL section. It is just a bit of not enough information.

In the Query section, normally this goes through some checking (especially for HSQLDB embeded) to insure some syntax. When table or field names are not quoted they are accepted as all caps. This is what HSQLDB embedded and Firebird expect. If in Query mode you turn on Run SQL command Directly (only mode used in Tools->SQL) then you will get the same error as what you received for the Update statement. With that, there is no checking by Base. It is just passing the SQL through to the database. Other databases may be looking for lower case table and field names by default. Some will convert unquoted to lower case regardless what is sent (example: PostgreSQL), even mixed case. Always refer to the documentation for the database and version you are using.

And yes, NULL is nothing. You cannot even compare to it. As you noted, you can only see if it is NULL or not.