LO base tool/sql update statement problems

base

asked 2020-05-18 18:16:04 +0100

abcd
1 ●1 ●4 ●6

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.

edit retag flag offensive close merge delete

add a comment

1 Answer

Sort by » oldest newest most voted

answered 2020-05-18 18:35:00 +0100

cpb
156 ●3 ●5

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")

edit flag offensive delete link

Comments

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!

abcd ( 2020-05-19 07:38:01 +0100 )edit

add a comment

Question Tools

1 follower

Stats

Asked: 2020-05-18 18:16:04 +0100

Seen: 66 times

Last updated: May 18 '20

LO base tool/sql update statement problems edit

1 Answer

Comments

Question Tools

Stats

Related questions

LO base tool/sql update statement problems