DELETE specific rows matching all values

Question

Say I have a table

SELECT * INTO MyTable FROM (
  VALUES ('col1_a', 'col2_a', 'col3_a', 'coln_a'),
         ('col1_b', 'col2_b', 'col3_b', 'coln_b'),
         ('col1_c', 'col2_c', 'col3_c', 'coln_c')
) t (col1, col2, col3, coln)

I only want to remove rows that correspond to each specific column. I find that rows are very similar to just one or two columns.

Of course I can write

DELETE MyTable WHERE col1='col1_a' AND col2='col2_a' AND col3='col3_a' AND coln='coln_a'
DELETE MyTable WHERE col1='col1_b' AND col2='col2_b' AND col3='col3_b' AND coln='coln_b'

But this is too annoying. I want to know whether it is possible to write this conveniently, simply by indicating the values? I tried this

DELETE MyTable FROM (
  VALUES ('col1_a', 'col2_a', 'col3_a', 'coln_a'),
         ('col1_b', 'col2_b', 'col3_b', 'coln_b')
) t (col1, col2, col3, coln)

but it just deleted all lines in MyTable, please help.

Answer 1

You can use INNER JOINin your expression DELETE:

DELETE t1
FROM MyTable t1
INNER JOIN (
    SELECT *
    FROM (VALUES 
        ('col1_a', 'col2_a', 'col3_a', 'coln_a'),
        ('col1_b', 'col2_b', 'col3_b', 'coln_b')
    )AS a(col1, col2, col3, coln)
) t2
    ON t2.col1 = t1.col1
    AND t2.col2 = t1.col2
    AND t2.col3 = t1.col3

---

As Frisbee commented , you can remove SELECT *:

DELETE t1
FROM MyTable t1
INNER JOIN (
    VALUES 
        ('col1_a', 'col2_a', 'col3_a', 'coln_a'),
        ('col1_b', 'col2_b', 'col3_b', 'coln_b')    
) AS t2(col1, col2, col3, coln)
    ON t2.col1 = t1.col1
    AND t2.col2 = t1.col2
    AND t2.col3 = t1.col3