Join deletion with same syntax in mysql and h2

Question

I have a production database in mysql and do some Junit tests in H2.
I am struggling to find a syntax that would both work in tests and production to delete records in 2 tables.
SQL queries are sent to the database using

jdbctemplate.update(sqlQuery, queryArgs);

So far I have found queries that work on mysql but not on H2 and vice versa.

I have 2 tables : requests and responses which have 2 columns in common datetime and milisec. The couple (datetime, milisec) is a primary key on requests, there can be several responses for the same couple.

I want to delete the responses that matches some of the requests.

This requests works fine with my sql but not with H2 :

DELETE r.*
FROM responses AS r NATURAL JOIN Call_requests AS s
WHERE -- criteria on call_requests table

The closest I have come with would be

DELETE FROM responses
where (datetime, milisec) in 
(select datetime, milisec from requests where -- criteria)

this upper version works with mysql not with h2, where as the following with parentheses around the tuple is the reverse (works with h2, not mysql)

DELETE FROM responses
where (datetime, milisec) in 
(select (datetime, milisec) from requests where -- criteria)

Regine

Oh, r.* works !?!? I didn't know you could do that! – Littlest

I wonder what MySQL thinks .* means in this context. – Littlest

Not a lot of answers or other comments on this one... I have reengineered the data base to have a unique index key that can make the join between the 2 tables. Solved the problem... – Regine

@PrDP Hi. Your edit dropping a tag really made no difference whatsoever & should not have been approved. Moreover it is not appropriate to add tags just because they technically include topics of (either one or of multiple) other tags; it is better to tag as specifically as possible. Please read about editing under approval at help center & by googling about that with 'stackexchange'. Please make substantive edits. – Demission

Hi! I have no idea about H2 but you can try one more syntax working for MySQL: DELETE responses FROM responses JOIN Call_requests ON Call_requests.Field_name = responses.Field_name WHERE <your_criteria> – Gosling

Answer 1

You may have to use a slightly different approach since H2 and MySQL handle tuple comparisons slightly differently.

Instead of trying to use a tuple comparison (i.e., comparing (datetime, milisec) from requests to (datetime, milisec) from responses), you could do separate comparisons for datetime and milisec.

To delete matching records from two tables, requests and responses, using the same SQL code for both MySQL and H2 databases this should work:

DELETE FROM responses
WHERE EXISTS (
    SELECT 1 
    FROM requests 
    WHERE responses.datetime = requests.datetime 
    AND responses.milisec = requests.milisec 
    -- add more criteria here if you need
)

Here we're removing entries from responses if there's at least one entry in requests with the same datetime and milisec.

Cobb