How to Delete Using Join EXASol

Question

I want to delete some records from a table using Join in EXAsol.

I am trying -

Delete tran
FROM 
Transactions tran
INNER JOIN 
Employees e
ON tran.ID = e.Transaction_ID
WHERE e.Name = 'Abhisar';

The error I am getting is -

syntax error, unexpected identifier_chain2, expecting FROM_ or '*' [line 1,column 8]

Answer 1

DELETE FROM tab1 a 
WHERE EXISTS (SELECT 1 FROM tab2 b WHERE a.id=b.id);

This is as good as JOIN and uses index internally. You may verify it if you enable profiling and check it after query execution.

Answer 2

The original error message is due to the 'TRAN' immediately following the 'DELETE' - see the Exasol user manual - the only valid ANSI SQL options for DELETE are 'DELETE FROM..' or 'DELETE * FROM...'

There are then various options as described above to pick the records which are to be deleted. Personally I tend to use the 'WHERE .. IN..(SELECT...)' method as I think this is clearer and therefore easier to maintain.