Apache OpenOffice Community Forum

Hello there!

I have an original table where I want to update a column with values from another table. How do I do that?

My original table is named Land and the table that contains the new values is Landsnummer, and the corresponding columns are both called Land. I’ve tried to run an SQL-expression like this in Base but it doesn’t work:

UPDATE "Land" SET "Land"."Landsnummer" = "Landsnummer"."Landsnummer" WHERE "Land"."Land" = "Landsnummer"."Land"

I might also mention that I only work inside Base. Not connected to any external database or so.

It is not wholly clear which database you are using with Base: it is shown at the left end of the status bar. If it is HSQL 1.8 that is embedded in Base the syntax can be found in the Chapter 9 of the User Guide at:
http://www.hsqldb.org/doc/1.8/guide/ch0 ... te-section
The Base GUI does not handle Update queries, so it will be necessary to enter such code from the menu bar using Tools>SQL.. to execute the SQL statement directly without being parsed by the Base GUI.

The status-bar in Base says "embedded database" and HSQL.
I've read that chapter 9 and don't get any further.

and don't get any further

What error message do you get after executing the SQL code using Tools>SQL... menus?
My apologies but as I do not use either LO or Windows 7, I am not aware whether there may not be a bug using direct SQL Update statements with that combination.

The message I get is this:
"1: Column not found: Landsnummer.Land"

I have also tried to run a SELECT command before UPDATE, like this:

SELECT "Land"."Land", "Land"."Landsnummer", "Landsnummer"."Land",
"Landsnummer"."Landsnummer" FROM "Land", "Landsnummer"
WHERE "Land"."Land" = "Landsnummer"."Land";
UPDATE Land
SET "Land"."Landsnummer" = "Landsnummer"."Landsnummer"
WHERE "Land"."Land" = "Landsnummer"."Land";

...but that don't help.
Now I get the message:
"1: Table not found in statement [UPDATE Land]"
Tha SELECT-line works fine as a query though and the two tables are matched together.

I've thought about that - f it could be a bug in Base.
But since I'm a beginner at databases, sql and all there is to it, it's more likely that the bug's in my head!

I fear the problem is outwith my limited experience.

Hopefully some members of this forum with better skills and wider experience than I will come to your help.

FWIW, before assuming a bug I would test the same syntax on a token table constructed with distinctly different names for the two tables and for the various implicated field: it is conceivable that confusion arises from the repeated use of the name "Landsnummer" for one of the tables and for fields in both tables. Should the problem persist, it MAY indicate a bug. You could check to see if a similar bug has been reported, and consider reporting one if it has not been - see http://forum.openoffice.org/en/forum/vi ... t=166#p713

I'm not as experienced as Arineckaig, but...

Ramne wrote:"1: Table not found in statement [UPDATE Land]"

Since you're working with case-sensitive names, you'll need double-quotes around "Land". The above error reflects the fact that unquoted names are rolled into ALL_UPPER_CASE by the engine in compliance with the SQL-standards. So the table LAND was not found.

Ramne wrote:...it could be a bug in Base.

No. In this case the SQL is passed directly to HSQLDB when using the SQL Console (Tools > SQL...). So if this is a bug, then it's in HSQLDB 1.8.0.10 which is no longer updated. You'd have to reproduce the bug in HSQLDB 2.x in order to submit a valid bug report.

I agree with Arineckaig. This 'bug' may be caused by the reuse of names. But typically, the use of aliases can effectively circumvent such bugs without renaming the tables or fields permanently. Although I haven't tried aliases in an UPDATE statement.

Give the following a try.

IT MUST BE RUN IN DIRECT SQL MODE. (menu: Tools > SQL...)

Thank you chrisb!
It works like a charm!
I noticed when trying different ways of executing the command that if I left out the "where exist"-statement all values that weren't matched in the Land-table became blank.

Many thanks to all of you who helped on this problem!

/Ramne