Ask Your Question
0

Update using the results of a sub-query

asked 2017-10-20 07:48:25 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have two tables, a master customer table and a customer update table, which are imported from Calc tables. Some of the rows in the update table have customer ids, and some don't. This update is to take the pID's from the update table and update the null pID's in the master customer table:

update "tbl_Import" set "tbl_Import"."pID" = (select "tbl_People"."pID" from "tbl_People" where "tbl_Import"."Nachname" = "tbl_People"."Nachname" and "ti."Vorname" = "tbl_People"."Vorname" and rownum < 2) where "tbl_Import"."pID" is NULL

The message I get from Base when I execute this from Tools->SQL is:

1: Unexpected token: VORNAME in statement [update "tbl_Import" set "tbl_Import"."pID" = (select "tbl_People"."pID" from "tbl_People" where "tbl_Import"."Nachname" = "tbl_People"."Nachname" and "ti."Vorname]

This works just fine when I enter it in an Oracle database with the same tables and data. Does Base not recognize sub-queries?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-10-20 08:37:09 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Ah, stupid syntax error. I withdraw the question. Correct query is:

update "tbl_Import" set "tbl_Import"."pID" = (select "tbl_People"."pID" from "tbl_People" where "tbl_Import"."Nachname" = "tbl_People"."Nachname" and "tbl_Import"."Vorname" = "tbl_People"."Vorname") where "tbl_Import"."pID" is NULL

But I do find it irritating that it is not possible to use rownum < 2 for this. Seems that Base does not recognize this. Or is there a substitute?

edit flag offensive delete link more

Comments

This is not a problem with Base. Base defaults to HSQLDB v1.8 (shipped with LO) which is very old and has its' limitations. Newer versions of HSQLDB have expanded capabilities. Refer to documentation for use - click here. Possibly related to your situation, see this post.

Ratslinger gravatar imageRatslinger ( 2017-10-20 16:49:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-20 07:48:25 +0200

Seen: 114 times

Last updated: Oct 20 '17