0

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 :-)

Community
  • 1
  • 1
Jefftopia
  • 2,105
  • 1
  • 26
  • 45
  • Are there more columns in the row? And do you actually want to delete rows from the table or just a `SELECT` statement to retrieve your desired result? – Erwin Brandstetter May 16 '13 at 20:57

4 Answers4

1

Try something like:

DELETE FROM your_table_name
WHERE col2 = 'y'
  AND col1 IN (SELECT col1
              FROM your_table_name 
              WHERE col2 = 'x')
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
1

Adding to Igor's answer, you could then add a trigger to do this automatically if that is part of your workflow.

create or replace function auto_delete_y_rows() returns trigger as $$
begin
  delete from tbl
  where col2 = 'y'
  and col1 = new.col1;

  return null;
end;
$$ language plpgsql;

create trigger auto_delete_y_rows
after insert or update on tbl
for each row
  when (new.col2 = 'x')
execute procedure auto_delete_y_rows();
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • At this time I do not require the additional code, although I will save it for future reference. Thank you! – Jefftopia May 16 '13 at 21:12
1

Select good rows

SELECT DISTINCT ON (col1)
       col1, col2  -- add more columns
FROM   tbl
ORDER  BY col1, col2;

Short and fast, and it's easy to include more columns. Explanation and links for the technique:
Select first row in each GROUP BY group?

Delete bad rows

DELETE FROM tbl t1
USING  tbl t2
WHERE  t2.col1 = t1.col1
AND    t2.col2 = 'x'
AND    t1.col2 = 'y'

IN is notoriously slow with big lists.
Add the table another time with the USING clause (self-join) to make this shorter and faster.
Verify performance with EXPLAIN ANALYZE.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Do you really want to delete rows? Or do you just want a query that returns what you want?

For the data you have provided, the following gives you the results you want:

select col1, min(col2) as col2
from t
group by col1;

An alternative way of expressing your logic is something like:

select t.*
from t
where not (t.col2 = 'Y' and
            exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 = 'X')
          )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Ha, the first worked after I changed min to max. Never would have thought min and max working on text variable, but there you have it. I don't *have* to delete them, but I need to either delete the rows or store the output into a new table. – Jefftopia May 16 '13 at 21:03