delete rows in postgresql

Question

I have a log table as follows:

id  status1 status2  ref  dateofchange
1     10      12      33     1.1.10
2     10      12      34     1.1.15
3     5        8      14     1.5.10
4     10      12      33     2.1.10

and another table tab as follows:

id ref qty  commitdate
1   17  5    1.1.10
2   33  8    1.1.10
3   34  7    1.12.14
4   34  8    1.2.16
5   34  8    1.1.15

I have a query which gives me rows from log table:

select *
from log
where status1=10 and status2=12

this gives:

id  status1 status2  ref  dateofchange
1     10      12      33     1.1.10
2     10      12      34     1.1.15
4     10      12      33     2.1.10

For each of these rows I want to delete all rows from tab where log.ref=tab.ref and tab.commitdate<=log.dateofchange

after deletion tab table should look like:

id ref qty  commitdate
1   17  5    1.1.10
4   34  8    1.2.16

i tried to do it with WITH query:

With l as (
    select *
    from log
    where status1=10 and status2=12
)
delete from tab where l.ref=tab.ref and tab.commitdate<=l.dateofchange

However this does not work.

ERROR: missing FROM-clause entry for table "l"

How can I do that?

score 1 · Accepted Answer · 2016-02-29 08:09:04Z

1

Joining a table in a DELETE statement has a different syntax as in the update or select statement. You need to use USING in order to join a table in a delete statement

with l as (
    select *
    from log
    where status1=10 and status2=12
)
delete from tab 
using l
where l.ref=tab.ref 
  and tab.commitdate <= l.dateofchange;

or without the CTE:

delete from tab 
using log l
where l.ref = tab.ref 
  and l.status1 = 10
  and l.status2 = 12
  and tab.commitdate <= l.dateofchange;

This can also be written without a join, using a co-related sub-query:

delete from tab
where exists (select *
              from log as l
              where l.status1 = 10 
                and l.status2 = 12
                and tab.commitdate <= l.dateofchange);

edited Feb 29, 2016 at 8:09

answered Feb 29, 2016 at 8:07

user330315

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

4 Comments

java Over a year ago

I don't understand the co-related sub-query. I don't want to delete everything from tab... I want to delete only rows where the ref is the same and the date is earlier than what is written in log table.

user330315 Over a year ago

@java: that's what the exists operator in the where clause is for. The statement does not delete all rows in tab - only those, where a row in the sub-query exists (hence the name).

java Over a year ago

But how dows it check the dates? dates aren't neccecrly a match.

user330315 Over a year ago

@java: the check is in the sub-query: and tab.commitdate <= l.dateofchange

Giorgos Betsos · Accepted Answer · 2016-02-29 08:06:41Z

1

In order to JOIN you need a USING clause:

WITH l AS (
    SELECT *
    FROM log
    WHERE status1=10 AND status2=12
)
DELETE FROM tab 
USING l
WHERE l.ref=tab.ref AND tab.commitdate <= l.dateofchange;

answered Feb 29, 2016 at 8:06

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Collectives™ on Stack Overflow

delete rows in postgresql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related