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

[koolninja]

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 out to run update Query

[Ratslinger]

Queries allow only Select statements. Other types of SQL (such as Insert) need to be run in Tools->SQL.. or in another program such as Workbench.

Edit 8/11/16:

In reply to your last comment, a basic practice in database design is not to save data which can be calculated from somewhere else. I have many such tables which are constantly being updated and are used to generate balances. When needed, these totals are generated from these tables to get the most current information. That being said, here is a macro for your SQL:

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 ""Total"" SET ""Total"".""IssueTotal"" =" &_
         " (SELECT SUM( ""Issue"".""Quantity"") FROM ""Issue""" &_
         " WHERE ""Issue"".""ProductName""=""Total"".""ProductName""" &_
         " GROUP BY ""Issue"".""ProductName"")"
    oStatement.executeUpdate( sSQL )
 End Sub

This statement is based strictly upon the information in you question.

[koolninja]

Basic syntax error, Expected:".
highlight this start portion " WHERE
please show me right way to run it without error

[Ratslinger]

Statement fixed - I did mention before that this type of formatting was a pain in the neck!

[koolninja]

Thanks its work…

[jimk]

“Queries allow only Select statements” is not correct. From https://stackoverflow.com/a/34562420/5100564: “Under some circumstances, it is possible to create an update query [in Base].” I’m not necessarily recommending it, but simply pointing out that it’s possible.

[peterwt]

As @Ratslinger said you can only run a Select statement in a Query. Your Update SQL needs to be run in Tools > SQL. This means you cannot save this for future use.

You can however run this SQL in a Macro which can then be run whenever you need to.

[koolninja]

Thanks for putting Off my confusion.
Any micros statment for Updating…

[Ratslinger]

Since there has been no response to your statement yet, I am against creating a macro for SQL just so it can be saved. You can just as easily save it in a writer document along with other SQL statements for use in various circumstances. Beside, formatting SQL in a macro is a pain in the neck using mixed case fields. Also, it seems, based upon your SQL, you are just duplicating what is in one table into another. There is no need to save it twice. It’s always available in the Issue table.

[koolninja]

“Issue” table always changing as data enter, so “Total” table need to be updated with reference of “Issue” table. always need SQL tool to update it, so macro in button type will make simple to update…

[Xiang_rose]

It’s 2019, but…

update Total, (SELECT Issue.ProductName as productname, SUM(Issue.Quantity) as sumtotal
    FROM Issue, Total where Issue.ProductName = Total.ProductName) as issuetotal
set Total.IssueTotal = issuetotal.sumtotal
where Total.ProductName = issuetotal.productname;

source: php - MYSQL Update using sum() result across multiple tables - Stack Overflow