LO base tool/sql update statement problems

[abcd]

I am getting crazy to update in BASE a table column with the values of another table of the same database ( hsqldb).
The most “standard” way in SQL is using a JOIN:

UPDATE “table1” set “table1”.“column1” = “table2”.“column1”
JOIN “table2” ON “table2”.“id” = “table1”.“id”
where […];

Unfortunately this statement does not work. An error regarding the not retreived “table2”.“column1” is issued. I double checked for syntax issues. A SELECT statement with the same join works like a charm.

Is there any other way?

Thanks, (Linux, LO 6.3.x, hsqldb)

P.S. I need to use the double quotes to reference the tables/columns.

[cpb]

please try this

UPDATE "table1" set "table1"."column1" = 
	(select "column1" from "table2" where "table2"."id" = "table1"."id")
--code below this line is essential in order to avoid null input
where 1 in
	(select 1 from "table2" where "table2"."id" = "table1"."id")

[abcd]

Thank you very much. I tried the proposed solution and I got the error “Single value expected”. After that I investigated further and discovered that the table2.id had some duplicates! Thank you very much again. Your help was very appreciated!