Ask Your Question
1

How to update multiple fields in LO Base?

asked 2016-11-20 03:27:47 +0200

Quarto Die gravatar image

updated 2016-11-20 03:32:26 +0200

I would like to update several fields from table2 into table 1 in LIBRE OFFICE BASE using SQL based on the equivalence of one ID field in both tables. I have tried the following solutions which I found online and most seem to have failed when the sql sequence met the first parentheses except for the Merge attempt which failed on its own. Here is what I have tried and the results - DOES ANY ONE HAVE ANYTHING THAT WORKS?:

Try #1 UPDATE table1 t1 SET (t1.column1, t1.column2) = (SELECT t2.column1, t2.column2 FROM table2 t2 WHERE t1.id = t2.id) WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t1.id = t2.id )

----*Failed: Unexpected token: ( in statement [update "T1" set (]

Try #2

UPDATE (SELECT t1.id, t1.column1, t1.column2, t2.column1, t2.column2 FROM table1 t1, table2 t2 WHERE t1.id = t2.id) SET t1.column1 = t2.column1, t1.column1 = t2.column1

-----Failed* Unexpected token: ( in statement [UPDATE (]

Try #3

merge into t2 t2 using (select * from t1) t1 on (t2.user_id = t1.user_id) when matched then update set t1.c1 = t2.c1 , t1.c2 = t2.c2

---- Failed: Unexpected token: MERGE in statement [merge]

Try#4

update t1 set (c1, c2, c3) = (select c1, c2, c3 from t2 where t2.user_id = t1.user_id) where exists (select * from t2 where t2.user_id = t1.user_id)

----*Failed: Unexpected token: ( in statement [update "T1" set (]

Try#5

update table1 t1 set ( t1.column1, t1.column2 ) = ( select t2.column1, t2.column2 from table2 t2 where t2.column1 = t1.column1 ) where exists ( select null from table2 t2 where t2.column1 = t1.column1 );

---Failed * ERROR MESSAGE "t1" set (]

DOES ANYONE HAVE AN SQL COMMAND THAT WOULD WORK WITH BASE IN THIS INSTANCE? THANK YOU!

edit retag flag offensive close merge delete

Comments

Where are you trying to run these statements? They cannot be run in a Query.

Ratslinger gravatar imageRatslinger ( 2016-11-20 03:31:19 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2016-11-20 06:37:37 +0200

Ratslinger gravatar image

updated 2016-11-20 06:42:14 +0200

The database you are using has an effect on what syntax to use. If you are using are the embedded database which comes with Base, then this will work (this also works in newer HSQL versions):

UPDATE "Table1"
   SET "column1" = (SELECT "column1" FROM "Table2" WHERE "Table1"."id"  = "Table2"."id"),
          "column2" = (SELECT "column2" FROM "Table2" WHERE "Table1"."id"  = "Table2"."id")

If you are using a newer version of HSQL (as in some split DB's with v2.3 or such) then this will work:

UPDATE "Table1"
   SET ("column1", "column2") = (SELECT "column1", "column2" FROM "Table2"
      WHERE "Table1"."id"  = "Table2"."id")

You must run these from the opening .odb menu Tools->SQL... and not in a query.

edit flag offensive delete link more

Comments

Thank you very much for your answers. The first one worked fine as prescribed.

Quarto Die gravatar imageQuarto Die ( 2016-11-23 05:16:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-20 03:27:47 +0200

Seen: 305 times

Last updated: Nov 20 '16