Create an update query

[JoeCastor]

One last time before I give it a rest, I would like to create a query to update two dates (MemberThruDate & LastRenewalDate) in family member records (MemberMaster) using a new View (View_PriMemDates) containing the source data. The foreign key PrimaryID in the MemberMaster is used to select the View record. I have made an SQL Command script that executes and performs correctly, however I’m trying for this to be run from the Query menu by someone not confident to run an SQL command script. I’ve tried various versions by starting with SELECT with no success. Thanks in advance for any insights. The working script is:

MERGE INTO "MemberMaster" USING "View_PriMemDates" ON "MemberMaster"."PrimaryID"  "View_PriMemDates"."MemberID" AND  "MemberMaster"."MemberType" = 'F' WHEN MATCHED THEN UPDATE SET "MemberMaster"."MemberThruDate" = "View_PriMemDates"."MemberThruDate", "MemberMaster"."LastRenewalDate" = "View_PriMemDates"."LastRenewalDate"

[Ratslinger]

@JoeCastor Only SELECT statements can be run from the query section. ALL others (basically anything which modifies the DB) needs to run from Tools->SQL or from a macro triggered by a button or otherwise.

Edit:

Don’t think you want to attempt, but…

Macro to run SQL:

Sub executeMergeSQL
    oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM_NAME_HERE")
    stSQL = "MERGE INTO ""MemberMaster"" USING ""View_PriMemDates"" ON ""MemberMaster"".""PrimaryID""  ""View_PriMemDates"".""MemberID"" AND  ""MemberMaster"".""MemberType"" = 'F' WHEN MATCHED THEN UPDATE SET ""MemberMaster"".""MemberThruDate"" = ""View_PriMemDates"".""MemberThruDate"", ""MemberMaster"".""LastRenewalDate"" = ""View_PriMemDates"".""LastRenewalDate""
    oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object
    oStatement.executeUpdate(stSQL)
End Sub

This is NOT tested as I don’t have data. You could attach this to the Execute action event of a button on a form. You also need to modify the first line of code with your internal form name - usually MainForm. If you try, please use a backup. Again have not tested. You will notice the field/table name have extra quote marks. This is because the entire statement needs to be within quotes & the extra quotes preserve the original quotes.

[JoeCastor]

Thought I’d give one last try. Thanks for your response.

[jimk]

This is mostly accurate, but under certain circumstances, it is possible to create update queries in the Query section. See openoffice.org - How to create an update query with Open Office Base? - Stack Overflow.

[Ratslinger]

@jimk (Heavy finger or slow response - got comment 5 x’s) Thank you for the Info. Both MySQL and PostgreSQL work with ‘Run SQL directly’ on. I also tested on split HSQL 2.x but did not work. It does allow you to save it though. v1.8 saves also but won’t run (storage at least!). Both tested versions of HSQL did need ‘Run SQL directly’ on just to save - if off it states syntax error. @JoeCastor may be interested in that.