SQL Query to delete from multiple tables

[Christopher Kitching]

Hi, can anybody help me with the following problem.

I am trying to use the following query:

DELETE FROM Characters, Character_Talents WHERE Characters.Character_ID = Character_Talents.Character_ID AND Characters.Character_ID = 1

I get the following error:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 unexpected token: ,
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:159)
at testapp.TestApp.main(TestApp.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.sun.javafx.application.LauncherImpl.launchApplicationWithArgs(LauncherImpl.java:389)
at com.sun.javafx.application.LauncherImpl.launchApplication(LauncherImpl.java:328)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:767)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ,
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
at net.ucanaccess.jdbc.Execute.executeWrapped(Execute.java:54)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:44)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:157)
... 12 more
Caused by: org.hsqldb.HsqlException: unexpected token: ,
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 18 more

[Gord Thompson]

It sounds like you want a parent/child relationship between the tables. If so, then do

ALTER TABLE Character_Talents ADD CONSTRAINT Character_Talents_FK1 FOREIGN KEY (Character_ID) REFERENCES Characters (Character_ID) ON DELETE CASCADE

and from that point on on when you do, say,

DELETE FROM Characters WHERE Character_ID = 1

then the rows will be deleted from both tables.

[Christopher Kitching]

Thanks Gord. I didn't even consider doing it that (the correct) way.

I'm still wondering, though, why the comma in my query causes a problem to UcanAccess. If my memory serves me right, that is the way we used to perform deletes 10 years ago, before I retired. Maybe I'm just getting senile.

Thanks again,

Chris