CODEWITHSUNDEEP
sqlpostgresql
asotos
asotos

Reputation: 337

Delete values in another table that appears once

I have the following tables:

movie_id  rent_id
1            1
2            2
2            3
3            3
3            4
3            5

rent_id  client_id  
   1         1
   2         1
   3         2
   4         4
   5         4

I'm trying to delete in the second table a row which movie_id = 2, but only if the respective rent_id appears once. For example, I want to delete a movie, and if a rent is based only on this movie, it should delete on second table, but in the case of rent_id = 3, I want keep it on the table, as there are other movies associated with it.

What I have tried is:

delete 
from en_aluguel 
where id_aluguel = (select id_aluguel 
                    from re_aluguel_filme 
                    where id_filme = 2 havin count(*) = 1);

but the result is not that I want

Upvotes: 1

Views: 55

Answers (3)

Evan Allen
Evan Allen

Reputation: 122

You have to have a group by to use having. Hope this works for you.

DELETE FROM public.movies
    WHERE movie_id in (select movie_id 
                         from movies 
                         group by movie_id 
                         having count(*) >= 2);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You seem to want:

delete from en_aluguel 
where id_aluguel in (select id_aluguel 
                     from re_aluguel_filme 
                     group by id_aluguel
                     having count(*) = 1 and    -- only one filrm
                            min(id_filme) = 2  -- and that is film "2"
                    );

Upvotes: 1

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could do this:

DELETE FROM en_aluguel e
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT movie_id, rent_id, 
            COUNT(*) OVER (PARTITION BY rent_id) AS cnt
        FROM re_aluguel_filme
    ) r
    WHERE r.rent_id = e.rent_id
        AND r.movie_id = 2
        AND r.cnt = 1
);

Tested in rextester

Upvotes: 0

Related Questions