Deleting using subquery and Join

Asked

Viewed 167 times

0

Guys, I’m getting started on 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 some table records EVENT_ITEM_PLACE_DYN_FIELD, but I can only delete the table records EVENT_ITEM_PLACE_DYN_FIELD provided that they exist in the table EVENT_ITEM_PLACE_DYN_FIELD_BKP, or I used the command EXISTS for this.

But I am having problems, because my query is bringing more than 7000 lines. I need my query to bring the same amount of rows as the temporary table. Follow 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));

Table Pks EVENT_ITEM_PLACE_DYN_FIELD :

  • EVENT_ITEM_ID
  • EVENT_PLACE_ID
  • COMMERCIAL_DYNAMIC_ID
  • DYNAMIC_FIELD_ID
  • LINE_NUMBER
join delete
  • 1

    What database are you using? And as you are starting with SQL, try to use simpler names for tables, so you don’t get lost.

  • I’m using PLSQL to do this.

1 answer

0

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

Usually I do like you, make first one SELECT returning all 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));

From here it is easy because the difference of DELETE just take the 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 "taggear" the SGBD used.

Obs 2: Check whether your SELECT is correct because what will bring the intersection of the two tables is the Join. The filter will simply check whether to return something or nothing SUBQUERY return something or nothing

  • Hello Jean, But the query that I use above is bringing more than 7000 lines, I believe it was in Cartesioan and that is why it is coming quite register. This is the big question, how to mount a query that can bring the same amount of line to make sure I’m deleting the right guys.

  • @Leo Well, that’s another matter, besides the more you’re using distinct, that means you’re not showing duplicate records and obviously if you want to delete all of your duplicates, then it depends on what you want to do you will delete much more than you are listing

  • @Leo maybe you would be + easy if you briefly explain what you want, use an example using the fiddle give an example of the expected result, finally taggear which SGBD vc is using and enjoy and read the help

  • 1

    Problem solved. I did as follows: select p.* From PR_EVENT_ITEM_PLACE_DYN_FIELD p Where (p.EVENT_ITEM_ID, p.event_place_id, p.commercial_dynamic_id, p.dynamic_field_id, p.line_number) in (Select t.EVENT_ITEM_ID, t.event_place_id, t.commercial_dynamic_id, t.dynamic_field_id, t.line_number From EVENT_ITEM_PLACE_DYN_FIELD_BKP t Where t.DYNAMIC_FIELD_ID In (39, 40)); This way you have brought the same amount contained in the BPK table by simply changing the select to delete. Thanks for Jean support

Browser other questions tagged join delete

You are not signed in. Login or sign up in order to post.