Hi Brian, I believe the problem is that the Sequoia parser is looking at the table name and getting confused by the fact that you a couple of table names with aliases. I just looked at the code in UpdateRequest.java and it basically looks for a word between UPDATE and SET.
So in summary Sequoia only supports a simple table name for UPDATE statements. The simplest solution is probably to split your SQL command into two statements in a transaction to maintain consistency. I should point out that we are more flexible in the commercial product if you are interested in trying a for-pay version. We support aliases and quotes. We would have to do a patch to get multiple table names to work in UPDATE (it's the first time I've seen it requested) but fixes like that are part of what you get in the commercial version. Thanks, Robert On 1/24/08 10:02 AM, "Brian" <[EMAIL PROTECTED]> wrote: > I have a query that tried to execute, but failed: > > update gangs as source, users as dest set source.money = source.money + > '100', dest.money = if((dest.money - '100') < 0,0,dest.money - '100') > where source.id = '4' and dest.id = '56270' and '100' <= dest.money > > this query was written in a way to be sure the "money" is added and > subtracted from proper places, even if the query it run at the same time > > The query failes with the errors: > > [12:44:42] ERROR > [org.continuent.myosotis.protocol.mysql.MySQLProtocolHandler] - Error > processing query: update gangs as source, users as dest set source.money > = source.money + '100', dest.money = if((dest.money - '100') < > 0,0,dest.money - '100') where source.id = '4' and dest.id = '56270' and > '100' <= dest.money > org.continuent.sequoia.common.exceptions.driver.DriverSQLException: > Message of cause: Unknown table 'gangs as source, users as dest' in this > UPDATE statement: 'update gangs as source, users as dest set > source.money = source.money + '100', dest.money = if((dest.money - > '100') < 0,0,dest.money - '100') where source.id = '4' and dest.id = > '56270' and '100' <= dest.money' > at > org.continuent.sequoia.driver.Connection.statementExecute(Connection.java:3031> ) > at > org.continuent.sequoia.driver.Statement.execute(Statement.java:468) > at > org.continuent.sequoia.driver.Statement.execute(Statement.java:445) > at > org.continuent.myosotis.protocol.mysql.MySQLProtocolHandler.processQuery(MySQL > ProtocolHandler.java:388) > at > org.continuent.myosotis.protocol.mysql.MySQLProtocolHandler.processCommand(MyS > QLProtocolHandler.java:207) > at > org.continuent.myosotis.protocol.mysql.MySQLProtocolHandler.processClientPacke > t(MySQLProtocolHandler.java:163) > at > org.continuent.myosotis.protocol.mysql.MySQLConnectionHandler.run(MySQLConnect > ionHandler.java:85) > at > java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java > :885) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907> ) > at java.lang.Thread.run(Thread.java:619) > Caused by: > org.continuent.sequoia.common.exceptions.driver.protocol.BackendDriverExceptio > n: > Unknown table 'gangs as source, users as dest' in this UPDATE statement: > 'update gangs as source, users as dest set source.money = source.money + > '100', dest.money = if((dest.money - '100') < 0,0,dest.money - '100') > where source.id = '4' and dest.id = '56270' and '100' <= dest.money' > SerializableStackTrace of each cause: > org.continuent.sequoia.common.exceptions.driver.protocol.BackendDriverExceptio > n: > Unknown table 'gangs as source, users as dest' in this UPDATE statement: > 'update gangs as source, users as dest set source.money = source.money + > '100', dest.money = if((dest.money - '100') < 0,0,dest.money - '100') > where source.id = '4' and dest.id = '56270' and '100' <= dest.money' > at > org.continuent.sequoia.controller.requests.UpdateRequest.parse(UpdateRequest.j > ava:278) > at > org.continuent.sequoia.controller.cache.parsing.ParsingCache.getParsingFromCac > heAndParseIfMissing(ParsingCache.java:298) > at > org.continuent.sequoia.controller.requestmanager.RequestManager.getParsingFrom > CacheOrParse(RequestManager.java:366) > at > org.continuent.sequoia.controller.requestmanager.RequestManager.scheduleExecWr > iteRequest(RequestManager.java:804) > at > org.continuent.sequoia.controller.requestmanager.distributed.DistributedReques > tManager.scheduleExecWriteRequest(DistributedRequestManager.java:1464) > at > org.continuent.sequoia.controller.virtualdatabase.protocol.DistributedStatemen > tExecuteUpdate.executeScheduledRequest(DistributedStatementExecuteUpdate.java: > 84) > at > org.continuent.sequoia.controller.virtualdatabase.protocol.DistributedRequest. > handleMessageMultiThreaded(DistributedRequest.java:157) > at > org.continuent.sequoia.controller.virtualdatabase.DistributedVirtualDatabase.h > andleMessageMultiThreaded(DistributedVirtualDatabase.java:335) > at > org.continuent.hedera.adapters.MulticastRequestAdapterThread.run(MulticastRequ > estAdapterThread.java:102) > > myosotis also throws 1 error: > 12:44:42,995 WARN controller.virtualdatabase.www_pirate Distributed > request update gangs as source, users as dest se... did not remove > itself from the total order queue > > Any insight would be appreciated... > > _______________________________________________ > Sequoia mailing list > [email protected] > https://forge.continuent.org/mailman/listinfo/sequoia -- Robert Hodges, CTO, Continuent, Inc. Email: [EMAIL PROTECTED] Mobile: +1-510-501-3728 Skype: hodgesrm _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
