[Solved] How to update between two tables?

Creating tables and queries
Post Reply
Ramne
Posts: 6
Joined: Thu Feb 14, 2013 12:01 am
Location: Sweden

[Solved] How to update between two tables?

Post by Ramne »

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.
Last edited by Ramne on Mon Feb 18, 2013 1:28 am, edited 2 times in total.
LibreOffice Version 4.0.0.3 on Windows 7 HSQL
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to update between two tables?

Post by Arineckaig »

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Ramne
Posts: 6
Joined: Thu Feb 14, 2013 12:01 am
Location: Sweden

Re: How to update between two tables?

Post by Ramne »

The status-bar in Base says "embedded database" and HSQL.
I've read that chapter 9 and don't get any further.
LibreOffice Version 4.0.0.3 on Windows 7 HSQL
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to update between two tables?

Post by Arineckaig »

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Ramne
Posts: 6
Joined: Thu Feb 14, 2013 12:01 am
Location: Sweden

Re: How to update between two tables?

Post by Ramne »

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! ;)
LibreOffice Version 4.0.0.3 on Windows 7 HSQL
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to update between two tables?

Post by Arineckaig »

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
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: How to update between two tables?

Post by DACM »

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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: How to update between two tables?

Post by chrisb »

Give the following a try.

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

Code: Select all

update "Land" set "Land"."Landsnummer" = 
(select "Landsnummer"."Landsnummer" from "Landsnummer" where "Land"."Land" = "Landsnummer"."Land")
where exists
(select "Landsnummer"."Landsnummer" from "Landsnummer" where "Land"."Land" = "Landsnummer"."Land")
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Ramne
Posts: 6
Joined: Thu Feb 14, 2013 12:01 am
Location: Sweden

Re: How to update between two tables?

Post by Ramne »

Thank you chrisb!
It works like a charm! :D
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! :super:

/Ramne
LibreOffice Version 4.0.0.3 on Windows 7 HSQL
Post Reply