CODEWITHSUNDEEP
sqlpostgresql
edhamilton812
edhamilton812

Reputation: 15

SQL Deleting from a join table not working

The below is returning a syntax error issue. Having searched thoroughly online, I cannot see why. Any ideas?

delete Tracks                                                                                                                                                     
from tracks                                                                                                                                                                      
left join releases                                                                                                                                                               
on tracks.label_id=releases.label_id                                                                                                                                             
where tracks.label_id = 185 
and releases.id = 4394 
and tracks.position = 1 
and tracks.name != 'Da Antidote';

The Syntax error is on line 1.

Upvotes: 0

Views: 84

Answers (3)

user1351763
user1351763

Reputation: 126

You can use an EXISTS clause:

DELETE FROM tracks t1 WHERE EXISTS (
    SELECT 1 FROM releases t2 WHERE 
        t1.label_id = t2.label_id
        AND yadda, yadda, yadda
);

Upvotes: 0

jpw
jpw

Reputation: 44881

If I remember correctly Postgres doesn't allow joins in DELETE, but you can use the USINGkeyword instead like described in the documentation:

DELETE FROM Tracks 
USING releases  
WHERE tracks.label_id=releases.label_id  
AND tracks.label_id = 185 
AND releases.id = 4394 
AND tracks.position = 1 
AND tracks.name != 'Da Antidote';

Upvotes: 2

Hassan
Hassan

Reputation: 1433

delete from tracks                                                                                                                                                                      
left join releases                                                                                                                                                               
on tracks.label_id=releases.label_id                                                                                                                                             
where tracks.label_id = 185 
and releases.id = 4394 
and tracks.position = 1 
and tracks.name != 'Da Antidote';

Upvotes: 1

Related Questions