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
– Erwin Brandstetter, Commented May 16, 2013 at 20:57

Ihor Romanchenko · Accepted Answer · 2013-05-16 19:54:27Z

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, 2013 at 19:54

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis de Bernardy · Accepted Answer · 2013-05-16 20:36:08Z

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, 2013 at 20:36

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

1 Comment

Jefftopia Over a year ago

At this time I do not require the additional code, although I will save it for future reference. Thank you!

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 16, 2013 at 21:11

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-05-16 19:54:08Z

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, 2013 at 19:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Jefftopia Over a year ago

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.

Collectives™ on Stack Overflow

UPDATE Postgresql where row...else delete

4 Answers 4

Comments

1 Comment

Select good rows

Delete bad rows

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Select good rows

Delete bad rows

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related