Esqueleto: How can I delete an item using a join

Question

Is the following query possible using esqueleto?

DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ItemID

I've tried:

delete $ 
  from $ \(table1 `InnerJoin` table2) -> 
    on (table1 ^. Table1ID  ==. table2 ^. Table2ItemID)

which, oddly enough, generated one of the only runtime errors I've ever seen in Haskell

ERROR:  syntax error at or near "INNER"
LINE 2: FROM "table1" INNER JOIN "table2" ON "tab...

(basically, it was unhappy that the DELETE was missing the "table1")

I've also tried adding a return value to the monad, which, like with select might add that missing value. But this fails because delete requires a monad of type m ().

Is it possible that this is just missing from Esqueleto?

I'm no SQL expert, but `DELETE FROM table1 INNER JOIN table2` doesn't look as a valid SQL statement, or at least not as a standard one. For instance PostgreSQL does not support it, and the manual suggests a subquery to accompllish that. http://www.postgresql.org/docs/9.4/static/sql-delete.html — chi, Sep 29 '15 at 07:49
@chi That is correct, but ```DELETE table1 from table1 INNER JOIN table2``` (note the extra `table1`) should work. My problem is that Esqueleto doesn't give me a way to add the extra ```table1```. Since there is no way to add this extra param, I tried without, thinking it might somehow be added, but it does just accept this faulty syntax at compiletime, which then leads to a runtime error. — jamshidh, Sep 29 '15 at 14:44
From the docs I linked, PostgreSQL does not support anything between `DELETE` and `FROM`. I agree on that Esqueleto should behave better, here. — chi, Sep 29 '15 at 17:23
@chi I did some further research, you are correct, this does seem to be an extension supported by certain DBs (ie- MySQL), but not in the standard, and particularly not in Postgresql. I am not sure of Esqueleto's philosophy on supporting extensions, but, as it turns out, I am actually using Postgresql (not sure how you knew that :) ), so it doesn't matter. Since this actually concludes this question for me, feel free to upgrade your comment to a soln, and I'll give you credit.... — jamshidh, Sep 29 '15 at 17:49

score 1 · Answer 1 · answered Aug 17 '19 at 05:48

1

I think it is very late to answer this question but you can use EXISTS

DELETE Table1
WHERE EXISTS (SELECT * FROM Table2 WHERE Table1.ID = Table2.ItemID)

answered Aug 17 '19 at 05:48

iman kazemi

522
5
15

score 0 · Answer 2 · edited Aug 14 '17 at 01:41

0

Try this.

delete $
from $ \(table1 `InnerJoin` table2) -> do
on (just (table1 ^. Table1ID  ==. table2 ^. Table2ItemID)

edited Aug 14 '17 at 01:41

Stephen Rauch

47,830
31
106
135

answered Aug 14 '17 at 01:25

Andrew G

3
1

score -3 · Answer 3 · answered Oct 09 '15 at 02:52

-3

In MS SQL you can use you can delete table using join in following manner

DELETE Table1,Table2  FROM Table1 INNER JOIN Table2
WHERE Table1.Id1= Table2.Id1 and Table1.Id1= '1'

answered Oct 09 '15 at 02:52

Moin Shirazi

4,372
2
26
38

I think you are missing the point of the question.... The question is, how do I convert this SQL query to Esqueleto. It doesn't seem to be possible. – jamshidh Oct 09 '15 at 03:33
Ohh sorry ... m not familiar with Esqueleto. . m sql a guy – Moin Shirazi Oct 09 '15 at 03:34

Esqueleto: How can I delete an item using a join

3 Answers3