1

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]
Abhisar Garg
  • 21
  • 4
  • Well, I don't know anything about exasolution (first time I even noticed that tag). But I wouldn't expect to see a Transaction_ID in an Employees table? Wouldn't it be more something like `JOIN Employees e ON t.emp_id = e.emp_id`? Btw, I don't know, but maybe `tran` could be a reserved word in exasolution? Like some abbreviation for `transaction`? – LukStorms Sep 25 '18 at 11:07
  • @LukStorms Select * works, so join is not an issue. Also, changed abbreviation from tran to a bunch of other words. Still the same issue. – Abhisar Garg Sep 25 '18 at 11:31
  • I don't know anything about the SQL syntax it uses. But perhaps using an IN instead? F.e. `DELETE * FROM Transactions WHERE ID IN (SELECT Transaction_ID FROM Employees WHERE Name = 'Abhisar')` ` – LukStorms Sep 25 '18 at 11:40
  • @LukStorms This statement would work perfectly fine in SQL. It's just Exasol (which is basically SQL) in which im facing this problem. Nevermind, I'll just wait for an answer. – Abhisar Garg Sep 25 '18 at 11:42
  • What about the ANSI SQL compliant `DELETE FROM Transactions WHERE ID IN (SELECT Transaction_ID FROM Employees WHERE Name = 'Abhisar')`? (I.e. LukStorm's suggestion, but without `*`.) – jarlh Sep 25 '18 at 12:13
  • @jarlh Yes, this can be done. But, would be much slower than a join functionality. – Abhisar Garg Sep 25 '18 at 12:57

2 Answers2

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.

wildraid
  • 126
  • 4
0

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.

Dave S
  • 71
  • 2