Query to delete rows from table

Question

I have two tables: datess, deletess, sample

I wrote a query like this:

DELETE FROM sample
WHERE sample_date_key IN
(SELECT date_key FROM datess WHERE s_date BETWEEN '2015-02-18' and DATE'2015-02-25');

But I have 2 rows and two columns in deletess:

start_date  |  end_date
------------+----------
2015-02-18  | 2015-02-18
2015-01-18  | 2015-01-18

I want to delete all the rows in sample with dates between start_date and end_date in deletess.

I tried the below code but got error:

ERROR: more than one row returned by a subquery used as an expression

(SELECT date_key FROM datess WHERE s_date BETWEEN (SELECT start_date FROM deletess) AND (SELECT end_date FROM deletess);

I appreciate any help. Thanks!

Answer 1

You can do it using WHERE EXISTS to check whether any row match the condition:

DELETE FROM sample
WHERE sample_date_key IN
(
  SELECT date_key
  FROM datess 
  WHERE EXISTS (
    SELECT 1
    FROM deletess
    WHERE s_date BETWEEN start_date
                     AND end_date
  )
);

Answer 2

join is better than in

DELETE s
FROM sample s
INNER JOIN datess d
    ON s.sample_date_key = d.date_key
INNER JOIN deletess sd
    ON sd.start_date <= d.s_date
       AND d.s_date <= sd.end_date;