Deleting using subquery and join

Question

Deleting using subquery and join

Navigation

#1 by (0 votes)

0

Dear, I'm starting in SQL and I'm having a hard time making a deletion using subquery and join.

I created a temporary table EVENT_ITEM_PLACE_DYN_FIELD_BKP with 7000 rows, I need to delete a records from the EVENT_ITEM_PLACE_DYN_FIELD table, but I can only delete the records of the EVENT_ITEM_PLACE_DYN_FIELD table since they exist in the EVENT_ITEM_PLACE_DYN_FIELD_BKP table, I used the EXISTS command for this.

But I'm having problems, because my query is bringing more than 7000 rows. I need my query to fetch the same amount of rows as the temporary table. Here's the query I'm using:

Select distinct p.EVENT_ITEM_ID
  From EVENT_ITEM_PLACE_DYN_FIELD p
 Inner Join EVENT_ITEM_PLACE_DYN_FIELD_BKP t
    On t.EVENT_ITEM_ID = p.EVENT_ITEM_ID
 Where Exists (Select  t.EVENT_ITEM_ID
          From EVENT_ITEM_PLACE_DYN_FIELD_BKP t
         Where t.DYNAMIC_FIELD_ID In (39, 40));

PKs of table EVENT_ITEM_PLACE_DYN_FIELD :

EVENT_ITEM_ID
EVENT_PLACE_ID
COMMERCIAL_DYNAMIC_ID
DYNAMIC_FIELD_ID
LINE_NUMBER

join delete

asked by anonymous 16.07.2018 / 21:36

1 answer

3d effect on building windows How can I simplify URLs for a website?

score 0 · Answer 1

In general you can do DELETE and UPDATE using joins without problem.

I usually do as you do, I first do a SELECT returning all the records I want to delete:

Select distinct p.EVENT_ITEM_ID
 From EVENT_ITEM_PLACE_DYN_FIELD p
 Inner Join EVENT_ITEM_PLACE_DYN_FIELD_BKP t On t.EVENT_ITEM_ID = p.EVENT_ITEM_ID
Where Exists (Select t.EVENT_ITEM_ID From EVENT_ITEM_PLACE_DYN_FIELD_BKP t Where t.DYNAMIC_FIELD_ID In (39, 40));

This is easy because the difference between DELETE is just to take DISTINCT :

delete p
 From EVENT_ITEM_PLACE_DYN_FIELD p
 Inner Join EVENT_ITEM_PLACE_DYN_FIELD_BKP t On t.EVENT_ITEM_ID = p.EVENT_ITEM_ID
Where Exists (Select t.EVENT_ITEM_ID From EVENT_ITEM_PLACE_DYN_FIELD_BKP t Where t.DYNAMIC_FIELD_ID In (39, 40));

Obs 1: Tested with TSQL since the OP forgot to "tag" the used DBMS.

Obs 2: Make sure your SELECT is correct because what will bring the intersection of the two tables is the join. The filter will simply check if it should return something or nothing if SUBQUERY returns something or nothing