Errors running UPDATE queries

[bbater1]

System: Windows 10, LO 7.2.2.2 (x64), Firebird

Ref question in 2016:

SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

my SQL statment is for update of “Total” table from “Issue” table:-> update “Total” set “Total”.“IssueTotal”=(SELECT SUM( “Issue”.“Quantity”) FROM “Issue” where “Issue”.“ProductName”=“Total”.“ProductName” GROUP BY “Issue”.“ProductName”) when this run’s in SQL then its show success anh update tbut when run in query then this error :- SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE whats is problem i can’t identify it. please help me…

REQUIREMENT
I have two tables, MARKEDREGISTER & FULLREGISTER which are largely identical structurally, with matching primary keys (“NamePostCodeYearType”). MARKEDREGISTER is a subset of rows in FULLREGISTER where Boolean column “MARKEDREGISTER”.“Voted” = TRUE. I need to replicate (i.e. UPDATE) the TRUE values from “MARKEDREGISTER”.“Voted” to “FULLREGISTER”.“Voted” where the keys match.

PROBLEM
I get errors whichever way I try to run this query. I have read what I can find regarding these errors on the forum, to no avail. Can someone please either tell me what I’m getting wrong, or give me some code that works.

I have tried:

1. UPDATE “FULLREGISTER”
   SET “FULLREGISTER”.“Voted” = “MARKEDREGISTER”.“Voted”
   JOIN “FULLREGISTER” ON “MARKEDREGISTER”.“NamePostCodeYearType” =
   “FULLREGISTER”.“NamePostCodeYearType”
   WHERE “FULLREGISTER”.“NamePostCodeYearType” =
   “MARKEDREGISTER”.“NamePostCodeYearType”

This gives “syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE” in the Base Query GUI and when run directly. Embedded in a macro, I get c, although the text colouring appears to indicate the syntax is OK.

2. MERGE INTO “FULLREGISTER”
   USING “MARKEDREGISTER”
   ON “MARKEDREGISTER”.“NamePostCodeYearType” =
   “FULLREGISTER”.“NamePostCodeYearType”
   WHEN MATCHED THEN
   UPDATE
   SET “FULLREGISTER”.“Voted” = “MARKEDREGISTER”.“Voted”

This gives “syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE” in the GUI, “firebird_sdbc error:
*Cursor is not open, caused by ‘isc_dsql_fetch’” when run directly and the same “Basic syntax error, Expected:”." in a macro.

3. Finally, I tried adapting Ratslinger’s solution for koolninja in 2016 and to run it via a macro:
   Sub RunUpdateSQL
   Dim oStatement As Object
   Dim sSQL As String
   if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
   Thisdatabasedocument.CurrentController.connect
   endif
   oStatement =
   Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
   sSQL = “UPDATE ““FULLREGISTER”” SET ““FULLREGISTER””.”“Voted”" =" &_
   " (SELECT ““MARKEDREGISTER””."“Voted”" FROM ““MARKEDREGISTER””" &_
   " WHERE
   ““MARKEDREGISTER””."“NamePostCodeYearType”"="“FULLREGISTER”"."“NamePostCodeYearType”"" &_
   " GROUP BY ““MARKEDREGISTER””."“NamePostCodeYearType”")"
   oStatement.executeUpdate( sSQL )
   End Sub

This gives the error “*Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)”

NOTE: Just thought to substitute “MARKEDREGISTER”.“Voted” for “FULLREGISTER”.“NamePostCodeYearType” in the GROUP BY - AND IT WORKS!

Sorry if I’ve wasted anyone’s time with this. But I’ll post anyway in case it’s of use to someone else.

[bbater1]

Already put this in main question, but just for closure:

NOTE: Just thought to substitute “MARKEDREGISTER”.“Voted” for “FULLREGISTER”.“NamePostCodeYearType” in the GROUP BY - AND IT WORKS!