I have seen various examples of delete row if a certain condition does not exist for another table.

I would like to delete row from a table if it already exists in another table. I came up with this query but it seems to be simple and I am worrying I am missing something.

DELETE FROM tmp t
WHERE (t.employee = (SELECT p.employee FROM position p
                         WHERE (t.column1 = p.column1 AND
                                t.column2 = p.column2)
                        )
      );

CodePudding user response：

That should be done using EXISTS. First, you don't run the danger of the subquery returning more than one row. Then, you can get a better execution plan.

DELETE FROM tmp
WHERE EXISTS (SELECT FROM position
              WHERE tmp.column1 = position.column1
                AND tmp.column2 = position.column2
                AND tmp.employee = position.employee);

CodePudding user response：

According to Postgres documents, you can use the better structure to delete items

DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

You can use the USING keyboard to join both tables.

Demo

delete from tmp t
using position p
where 
  t.column1 = p.column1 
  and t.column2 = p.column2
  and t.employee = p.employee;