Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

How to update multiple fields in LO Base?

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 t2.column1 = t1.column1, t2.column1 = t1.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 t2.c1 = t1.c1 , t2.c2 = t1.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!

How to update multiple fields in LO Base?

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 t2.column1 = t1.column1, t2.column1 = t1.column1t1.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 t2.c1 = t1.c1 t1.c1 = t2.c1 , t2.c2 = t1.c2t1.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!