Ask Your Question
0

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

asked 2016-08-09 13:28:32 +0200

koolninja gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Ratslinger
close date 2016-08-15 19:30:18.702456

3 Answers

Sort by » oldest newest most voted
0

answered 2016-08-09 16:13:17 +0200

Ratslinger gravatar image

updated 2016-08-12 19:54:16 +0200

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.

edit flag offensive delete link more

Comments

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

koolninja gravatar imagekoolninja ( 2016-08-12 19:45:58 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2016-08-12 19:55:14 +0200 )edit

Thanks its work......

koolninja gravatar imagekoolninja ( 2016-08-13 08:32:01 +0200 )edit

"Queries allow only Select statements" is not correct. From https://stackoverflow.com/a/34562420/...: "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.

Jim K gravatar imageJim K ( 2017-12-27 05:38:27 +0200 )edit
0

answered 2016-08-10 00:09:41 +0200

peterwt gravatar image

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.

edit flag offensive delete link more
0

answered 2016-08-10 18:16:07 +0200

koolninja gravatar image

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

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2016-08-11 01:20:10 +0200 )edit

"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...

koolninja gravatar imagekoolninja ( 2016-08-11 07:34:20 +0200 )edit

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: https://stackoverflow.com/questions/1...

Xiang_rose gravatar imageXiang_rose ( 2019-03-31 03:28:47 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2016-08-09 13:28:32 +0200

Seen: 2,048 times

Last updated: Aug 12 '16