How to Delete the record in multi-table SELECT query

[Karajan]

Dear H2 Database developers,

Hi, i'm working on a distributed CPNet system and using embedded H2
database as data persistent layer. i want to implement a logic:
a) select one record (one row) from each table (suppose we have 3
tables, A, B and C, each table contains several records) which
satisfies some pre-defined conditions,
b) and delete these records after selection instantly.
The two part should be performed in one atomic database operation.

The first part is very easy in SQL (using joint table query), but i
didn't found good solutions for the second part, i try to use jdbc
transaction:
1) set connection auto commit false,
2) prepare the following query and update

SELECT A.id,B.id,C.id FROM A,B,C WHERE <conditions> LIMIT 0,1

DELETE FROM A WHERE id IN (SELECT A.id FROM A,B,C WHERE true LIMIT
0,1);
DELETE FROM B WHERE id IN (SELECT B.id FROM A,B,C WHERE true LIMIT
0,1);
DELETE FROM C WHERE id IN (SELECT C.id FROM A,B,C WHERE true LIMIT
0,1);

3) commit the connection

it seems working when each table have some tokens, but when table A
have only one token before select, the last two delete update is not
working, since table A will be empty after first delete update and the
sub query will be empty.

Afterwards i try to use trigger. create trigger after select on tables
to delete the queried rows. but my code is not working, always give
the exception that the trigger class not found (source code attached
at the end of this email).

i'm not sure if my attempts are right or there are some more efficient
implementation for my logic, please give me some help and advice. Look
forward your reply.

Thanks.

Shang Kun

Source code for ClientTest.java
=================================================================

package org.scisaga.tetra.test;
import java.sql.*;

import org.h2.api.Trigger;
public class ClientTest {
public static void main(String[] args) throws ClassNotFoundException,
SQLException {
Class.forName("org.h2.Driver");
Connection c = DriverManager.getConnection("jdbc:h2:tcp://
127.0.0.1:12358/testNet", "", "");
Statement stmt = c.createStatement();
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS Persons(id INT,
lastname VARCHAR(255), firstname VARCHAR(255))");
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS Places(id INT, name
VARCHAR(255), address VARCHAR(255))");
stmt.execute("CREATE TRIGGER UP AFTER SELECT ON Persons FOR EACH ROW
CALL \"org.scisaga.tetra.test.ClientTest$UP\"");

for(int i = 0; i<5 ;i++){ stmt.executeUpdate("INSERT INTO Persons
VALUES("+i+", 'AAA', 'BBB')");}
for(int i = 0; i<7 ;i++){ stmt.executeUpdate("INSERT INTO Places
VALUES("+i+", 'CCC', 'DDD')");}

String sql = "SELECT * FROM Persons";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){ System.out.println(rs.getString(1)); }

sql = "SELECT * FROM Persons";
rs = stmt.executeQuery(sql);
while(rs.next()){ System.out.println(rs.getString(1)); }

stmt.executeUpdate("DROP TABLE Persons");
stmt.executeUpdate("DROP TABLE Places");
stmt.close();
c.close();
}

public static class UP implements Trigger {

public void init(Connection conn, String schemaName, String
triggerName, String tableName, boolean before, int type) {
// Initializing trigger
}

public void fire(Connection conn,
Object[] oldRow, Object[] newRow)
throws SQLException {

PreparedStatement prep = conn.prepareStatement(
"DELETE Persons WHERE id=?");
prep.setString(1, (String) oldRow[0]);
prep.execute();
}

public void close() {
// ignore
}

public void remove() {
// ignore
}

}
}

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

What about moving the rows into an intermediate table first?

create temp table temp as

SELECT A.id,B.id,C.id FROM A,B,C WHERE <conditions> LIMIT 0,1

DELETE FROM A WHERE id IN (SELECT A.id FROM temp);
DELETE FROM B WHERE id IN (SELECT B.id FROM temp);
DELETE FROM C WHERE id IN (SELECT C.id FROM temp);
drop table temp;

Regards,
Thomas

[Karajan]

It's works, Cool!
Thanks a lot :)

On May 12, 3:35 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Karajan]

To using the sql temp table solve this problem, when mulit-threads
from different JVM do the same operation, they should use different
temp table names to avoid thread conflicts.

But when you do this, the query speed become slower than expected, is
there some methods to help speed up this multi-table-select-delete
operation?

Thanks a lot!

Shang Kun

On May 12, 3:35 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> But when you do this, the query speed become slower than expected

Could you provide some details, and if possible a simple standalone test case?

> is there some methods to help speed up

It's hard to say without having detailed requirements.