Update using the results of a sub-query

[lyspon]

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?

[lyspon]

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?

[Ratslinger]

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.