Search code examples
sqlpostgresqlsql-updatedelete-row

UPDATE Postgresql where row...else delete

Here's what I have:

col1 | col2

------| ------
a.....| x......
a.....| y......
b.....| y......
c.....| y......
d.....| y......
d.....| x.....

Here's what I want:

col1 | col2

------| ------
a.....| x......
b.....| y......
c.....| y......
d.....| x......

So the idea is to remove any row where col1 is paired with y when it is also paired with x in a different row.

I'm very new to sql! Closest thing I could fine is this, but it's not helping...https://stackoverflow.com/editing-help

Thanks :-)

Solution

  • Try something like:

    DELETE FROM your_table_name
WHERE col2 = 'y'
  AND col1 IN (SELECT col1
              FROM your_table_name 
              WHERE col2 = 'x')