How to delete the same table fields with an sql query

Question

I'm having a hard time I have a lot of the same table fielt when inputting example:

---------------------------------<br/>
| id_pr       | id_pro   | id_cat  |
<br/>---------------------------------<br/>
| 1001        | 002      | 103     |
<br/>---------------------------------<br/>
| 1002        | 003      | 104     |
<br/>---------------------------------<br/>
| 1003        | 004      | 103     |
<br/>---------------------------------<br/>

id_cat field has the same data which is 103 how do i delete one of the same 103 data fields and when it is deleted data in

--------------------
| 1003 | 004 | 103 |
--------------------

also deleted, how do I enter the sql query ?

can you help me

Popeye · Accepted Answer · 2021-01-21 02:12:07Z

1

You should use exists as follows:

Delete from your_table t
 Where exists
       (Select 1 from your_table tt
         Where t.id_cat = tt.id_cat
           And t.id_par > tt.id_par);

answered Jan 21, 2021 at 2:12

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-01-21 01:50:11Z

0

You can keep the smallest id using join with an aggregation query:

delete t
    from t join
         (select id_cat, min(id_pr) as min_id_pr
          from t
          group by id_cat
          having count(*) > 1
         ) tt
         on t.id_cat = tt.id_cat and t.id_pr > tt.min_id_pr

answered Jan 21, 2021 at 1:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

url snort Over a year ago

I tried to enter the message in mysql but an error message appeared #1146 - Table 'data_story' doesn't exist

Gordon Linoff Over a year ago

@urlsnort . . . You need a table that does exist, I guess.

Collectives™ on Stack Overflow

How to delete the same table fields with an sql query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related