Base → LO Base import as SQL Update

[cbg.jahn]

Dear LO Base team:

The procedure of importing data from a spreadsheet into a LO Base table works as SQL Insert statement, as shown below in the LO Help forum. i.e., for adding data rows of NEW, DISTINCT primary keys into an existing / defined SQL table.

Importing and exporting data in Base

http://help.libreoffice.org/Common/Importing_and_Exporting_Data_in_Base

Is there a procedure working as SQL Update statement? i.e., for updating existing rows (EXISTING primary keys) in a SQL table?

Sincerely,

Ray

[business case]

Business operation always copes with dynamic data.

Client POs undergo frequent revisions of delivery date, purchase quantity, colors, etc. Client issues revision of multiple POs (batch mode) in spreadsheet. A partially automated procedure of data import for SQL Update, similar to that for SQL Insert above, is crucial for the business case of LO Base.

[cbg.jahn]

Hi, RatSlinger:

Thanks for 12-06 feedback.

Forms mandate manual input effort, row by row. Forms are not suited for batch import. Any guidance is appreciated on the following items of LO scripting.

Script examples (Basic, BeanShell, etc.) to reuse the code for SQL Insert?
Is the following script planning feasible?

Sincerely,
Ray

[script planning]

Two sections of JDBC code are needed per SQL Update syntax:

Update <… table …> Set <… tuple value …> Where <… match of primary keys …>

(1). Set (columns) = (values) → assignment, reuse code for SQL Insert.

(2). Where (keys) = (targets) → comparison, new code.

[tuple comparison]

www.sql-workbench.eu/dbms_comparison.html

none = Derby, Firebird, MS SQL Server,

full = HSQLDB, PostgreSQL, IBM DB2,

partial = Sqlite (no In), H2 (no In), MariaDB (no Between), MySQL (no Between), Oracle (only =),

[cbg.jahn]

12-10 repost as a comment, by deleting answer and repasting as comment, per 12-09 advice by RatSlinger. The forum tools did not work.

(1) repost as a question comment.

(2) repost as a comment under older questions.

[Ratslinger]

Hello,

There is no easy process to “update” table records in Base from Calc. You would need to do this via a form in Calc or if not using a form write specific macro(s) for the process.

[Ratslinger]

In response to your additional comments posted as an answer, it would appear that the scripting be placed in the Calc file and not in Base. This seems to be where the data is coming from. For code used in a similar process see this post → Calc BASIC + Firebird : How to add flush ?

It is just executing SQL through a macro.

[cbg.jahn]

Thanks for the link to template JDBC code in LO Basic.