Ask Your Question
0

Create an update query [closed]

asked 2017-09-30 21:32:03 +0200

Joe Castor gravatar image

updated 2017-09-30 21:38:00 +0200

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"
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-09-30 23:06:49.461528

1 Answer

Sort by » oldest newest most voted
0

answered 2017-09-30 22:32:38 +0200

Ratslinger gravatar image

updated 2017-09-30 23:49:04 +0200

@Joe Castor 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.

edit flag offensive delete link more

Comments

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

Joe Castor gravatar imageJoe Castor ( 2017-09-30 23:05:57 +0200 )edit

This is mostly accurate, but under certain circumstances, it is possible to create update queries in the Query section. See https://stackoverflow.com/questions/3....

Jim K gravatar imageJim K ( 2017-10-01 01:40:36 +0200 )edit

@Jim K (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. @Joe Castor may be interested in that.

Ratslinger gravatar imageRatslinger ( 2017-10-01 02:15:09 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2017-09-30 21:32:03 +0200

Seen: 432 times

Last updated: Sep 30 '17