Deleting with SQL Issue

Question

Question: Write a query to delete all videos that aren't available in DVD format.

Problem: Some videos can have multiple formats (eg VCR and DVD) but my query still deletes it regardless. So in reference to below I should still have Matrix and Tights in my database however it still deletes Matrix because its also available in VCR. Any ideas?

My Query (Should i even be using sub queries?)

DELETE FROM video 
WHERE VIDEO_ID IN (
    SELECT VIDEO_ID 
    FROM video_format 
    WHERE FORMAT_ID NOT IN (
        SELECT FORMAT_ID
        FROM format 
        WHERE FORMAT_DESC='DVD')) ;

Table: format

Column format_id, format_desc
            1      VCR
            2      DVD

Table: video

Columns: video_id, title
         1         Matrix
         2         Evil
         3         Tights

Table: video_format

Columns: video_format_id, video_id, format_id
                1            1           1
                2            1           2
                3            2           1
                4            3           2

score 0 · Answer 1 · answered Aug 12 '18 at 08:51

A better and faster approach to this would be the use of the Exists clause.

Delete from Video V where not exists
(Select Video_id from video_formt VF Join Format F on VF.Video_Format_id = F.Format_id 
where F.Format_Desc = 'DVD'
and v.video_id = VF.Video_id)

Note that Exists (or Not Exists) in this case will be faster compared to using a Not-In construct.

score -1 · Accepted Answer · answered Apr 11 '14 at 07:05

-1

DELETE FROM video 
 WHERE video_id not in (select video_id from video_format where format_id = 2 )

or (id U dont know format_id):

DELETE FROM video 
 WHERE video_id not in (SELECT video_id FROM video_format WHERE 
   format_id =  (SELECT FORMAT_ID
    FROM format 
    WHERE FORMAT_DESC='DVD'))

answered Apr 11 '14 at 07:05

Andrzej Reduta

767
1
7
15

I have to assume the user doesn't know the format_id tho. – Tzhari Apr 11 '14 at 07:07
U can use join instead of subquery. – Andrzej Reduta Apr 11 '14 at 07:15
Lets say speaking in an industry context, would it be advised to use a join rather then sub queries? Or it doesn't matter either way. – Tzhari Apr 11 '14 at 07:23
it depends, but in most cases i will prefer join – Andrzej Reduta Apr 11 '14 at 07:24
Something Like this? Although i couldn't get it working. `delete from video v inner join video_format vf on vf.video_id = v.video_id inner join format f on f.form_id = vf.form_id where f.desc != 'DVD';` – Tzhari Apr 11 '14 at 07:27

Deleting with SQL Issue

2 Answers2