UPDATE Postgresql where row...else delete

Question

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

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

score 1 · Accepted Answer · answered May 16 '13 at 19:54

1

Try something like:

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

answered May 16 '13 at 19:54

Ihor Romanchenko

24,934
7
45
42

score 1 · Answer 2 · answered May 16 '13 at 20:36

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();

answered May 16 '13 at 20:36

Denis de Bernardy

72,128
12
122
148

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

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

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.

score 0 · Answer 4 · answered May 16 '13 at 19:54

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

answered May 16 '13 at 19:54

Gordon Linoff

1,198,228
53
572
709

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

UPDATE Postgresql where row...else delete

4 Answers4

Select good rows

Delete bad rows