I don’t get it. A simple execute:
def up do
execute "DELETE FROM chat_messages cm
INNER JOIN chat_users cu
ON cu.id = cm.chat_user_id
WHERE cu.user_id = 'xyz';"
flush()
end
Syntax error near INNER. What is wrong? It is a postgres I am using.
You’re missing some keywords in the query: chat_messages cm should probably be chat_messages AS cm and the same for chat_users cu => chat_users AS cu.
Nope.
DELETE FROM chat_messages
INNER JOIN chat_users
ON chat_users.id = chat_messages.chat_user_id
WHERE chat_users.user_id = 'xyz';
Same error.
I tried some variations before, Checked postgres docs… Asked after trying myself. And yes, ANSI is with AS, but most of the databases make AS optional.
WHERE chat_users.id = ‘xyz’
At least there is a mismatch with the key You used with ON.
ON chat_users.id
I am affraid postgres does not allow deleting with joins https://www.postgresql.org/docs/current/static/sql-delete.html. You probably should use a subquery in a WHERE clause. Alternatively you could use USING option.
$ psql music_db
psql (10.4)
Type "help" for help.
music_db=# SELECT * FROM albums;
id | title | artist_id | inserted_at | updated_at
----+-------------------------------+-----------+----------------------------+----------------------------
1 | Kind Of Blue | 1 | 2018-06-09 13:28:31.214236 | 2018-06-09 13:28:31.214243
2 | Cookin' At The Plugged Nickel | 1 | 2018-06-09 13:28:31.224377 | 2018-06-09 13:28:31.224382
3 | You Must Believe In Spring | 2 | 2018-06-09 13:28:31.22988 | 2018-06-09 13:28:31.229885
4 | Portrait In Jazz | 2 | 2018-06-09 13:28:31.236809 | 2018-06-09 13:28:31.236814
5 | Live At Montreaux | 3 | 2018-06-09 13:28:31.243161 | 2018-06-09 13:28:31.243166
(5 rows)
music_db=# SELECT * FROM tracks;
id | title | duration | index | number_of_plays | album_id | inserted_at | updated_at
----+------------------------------------------+----------+-------+-----------------+----------+----------------------------+----------------------------
1 | So What | 544 | 1 | 0 | 1 | 2018-06-09 13:28:31.220724 | 2018-06-09 13:28:31.220731
2 | Freddie Freeloader | 574 | 2 | 0 | 1 | 2018-06-09 13:28:31.221914 | 2018-06-09 13:28:31.221919
3 | Blue In Green | 327 | 3 | 0 | 1 | 2018-06-09 13:28:31.222605 | 2018-06-09 13:28:31.22261
4 | All Blues | 693 | 4 | 0 | 1 | 2018-06-09 13:28:31.223244 | 2018-06-09 13:28:31.223249
5 | Flamenco Sketches | 481 | 5 | 0 | 1 | 2018-06-09 13:28:31.223782 | 2018-06-09 13:28:31.223787
6 | If I Were A Bell | 1006 | 1 | 0 | 2 | 2018-06-09 13:28:31.22586 | 2018-06-09 13:28:31.225865
7 | Stella By Starlight | 774 | 2 | 0 | 2 | 2018-06-09 13:28:31.226386 | 2018-06-09 13:28:31.226391
8 | Walkin' | 896 | 3 | 0 | 2 | 2018-06-09 13:28:31.226908 | 2018-06-09 13:28:31.226913
9 | Miles | 754 | 4 | 0 | 2 | 2018-06-09 13:28:31.227346 | 2018-06-09 13:28:31.22735
10 | No Blues | 1061 | 5 | 0 | 2 | 2018-06-09 13:28:31.227752 | 2018-06-09 13:28:31.227756
11 | B Minor Waltz (for Ellaine) | 192 | 1 | 0 | 3 | 2018-06-09 13:28:31.231742 | 2018-06-09 13:28:31.231746
12 | You Must Believe In Spring | 337 | 2 | 0 | 3 | 2018-06-09 13:28:31.232465 | 2018-06-09 13:28:31.232469
13 | Gary's Theme | 255 | 3 | 0 | 3 | 2018-06-09 13:28:31.232954 | 2018-06-09 13:28:31.232958
14 | We Will Meet Again (for Harry) | 239 | 4 | 0 | 3 | 2018-06-09 13:28:31.233493 | 2018-06-09 13:28:31.233497
15 | The Peacocks | 360 | 5 | 0 | 3 | 2018-06-09 13:28:31.233957 | 2018-06-09 13:28:31.233961
16 | Sometime Ago | 292 | 6 | 0 | 3 | 2018-06-09 13:28:31.234491 | 2018-06-09 13:28:31.234496
17 | Theme From M*A*S*H (Suicide Is Painless) | 353 | 7 | 0 | 3 | 2018-06-09 13:28:31.234969 | 2018-06-09 13:28:31.234974
18 | Without a Song | 485 | 8 | 0 | 3 | 2018-06-09 13:28:31.235393 | 2018-06-09 13:28:31.235398
19 | Freddie Freeloader | 454 | 9 | 0 | 3 | 2018-06-09 13:28:31.235874 | 2018-06-09 13:28:31.235879
20 | All of You | 489 | 10 | 0 | 3 | 2018-06-09 13:28:31.236314 | 2018-06-09 13:28:31.236319
21 | Come Rain Or Come Shine | 204 | 1 | 0 | 4 | 2018-06-09 13:28:31.23761 | 2018-06-09 13:28:31.237615
22 | Autumn Leaves | 360 | 2 | 0 | 4 | 2018-06-09 13:28:31.237979 | 2018-06-09 13:28:31.237984
23 | Witchcraft | 277 | 3 | 0 | 4 | 2018-06-09 13:28:31.238336 | 2018-06-09 13:28:31.23834
24 | When I Fall In Love | 297 | 4 | 0 | 4 | 2018-06-09 13:28:31.238679 | 2018-06-09 13:28:31.238683
25 | Peri's Scope | 195 | 5 | 0 | 4 | 2018-06-09 13:28:31.239039 | 2018-06-09 13:28:31.239043
26 | What Is This Thing Called Love? | 276 | 6 | 0 | 4 | 2018-06-09 13:28:31.239387 | 2018-06-09 13:28:31.239391
27 | Spring Is Here | 309 | 7 | 0 | 4 | 2018-06-09 13:28:31.23981 | 2018-06-09 13:28:31.239816
28 | Someday My Prince Will Come | 297 | 8 | 0 | 4 | 2018-06-09 13:28:31.240308 | 2018-06-09 13:28:31.240312
29 | Blue In Green | 325 | 9 | 0 | 4 | 2018-06-09 13:28:31.240724 | 2018-06-09 13:28:31.240729
30 | Anton's Ball | 761 | 1 | 0 | 5 | 2018-06-09 13:28:31.245552 | 2018-06-09 13:28:31.245556
31 | The Moontrane | 647 | 2 | 0 | 5 | 2018-06-09 13:28:31.246299 | 2018-06-09 13:28:31.246304
32 | Farallone | 805 | 3 | 0 | 5 | 2018-06-09 13:28:31.246836 | 2018-06-09 13:28:31.246841
33 | Song Of Songs | 844 | 4 | 0 | 5 | 2018-06-09 13:28:31.247288 | 2018-06-09 13:28:31.247293
(33 rows)
music_db=# DELETE FROM tracks tr INNER JOIN albums al ON tr.album_id = al.id WHERE al.title = 'Kind Of Blue';
ERROR: syntax error at or near "INNER"
LINE 1: DELETE FROM tracks tr INNER JOIN albums al ON tr.album_id = ...
^
music_db=# DELETE FROM tracks WHERE album_id IN (SELECT id FROM albums WHERE title = 'Kind Of Blue');
DELETE 5
music_db=# \q
Oh, ok, thought a join is allowed in postgres. The subquery made the job. Thanks 
The SQL standard doesn’t allow joins in DELETE statements. PostgreSQL does, extending the syntax with the USING clause.
© Copyright Elixir Forum | Terms | Privacy & Cookies
