Click here to Skip to main content
15,400,381 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I HAVE 3 row in table
Need to delete other except condition

0 items deleted

Table
id sifra
13 DWL00446
14 0.0455655.C
15 0.0455655.C

In this case need to remove just id 15

Thank you

What I have tried:

delete from dbo.trebovnica_roba_roba 
where id_fakture=7 and NOT EXISTS (select MIN(id), sifra from dbo.trebovnica_roba_roba where id_fakture=7 and rn='RN 002 2022' group by sifra)
Posted
Updated 20-Jun-22 0:49am
Comments
CHill60 20-Jun-22 5:48am
   
If you want to remove id 15 then why are you using id_fakture = 7 in your query?
Goran Bibic 20-Jun-22 5:51am
   
This are condition

id_fakture=7 and NOT EXISTS (select MIN(id), sifra from dbo.trebovnica_roba_roba where id_fakture=7 and rn='RN 002 2022' group by sifra)

if remove id_fakture=7 then delete all values in table

id_fakture is not id, id_fakture is control field for condition.
id is id in table
Richard Deeming 20-Jun-22 6:10am
   
If you want someone to help you fix your query, you need to provide a clear and complete description of what you are trying to do, what you have tried, and where you are stuck.

Providing a sample table with two columns, telling us you want to delete one row, and then giving us conditions based on columns and tables which aren't in your sample, isn't going to work.
Goran Bibic 20-Jun-22 6:35am
   
Simple question
Condition
How to remove all rows exept first minimal id and sifra

Table

id sifra id_fakture rn
13 DWL00446 RN 002 2022 7
14 0.0455655.C RN 002 2022 7
15 0.0455655.C RN 002 2022 7
16 DWL00446 RN 002 2022 7
17 DWL00446 RN 002 2022 7
18 0.0455655.C RN 002 2022 7
19 0.0455655.C RN 002 2022 7


Goran Bibic 20-Jun-22 6:35am
   
Simple question
Condition
How to remove all rows exept first minimal id and sifra

Table

id sifra id_fakture rn
13 DWL00446 RN 002 2022 7
14 0.0455655.C RN 002 2022 7
15 0.0455655.C RN 002 2022 7
16 DWL00446 RN 002 2022 7
17 DWL00446 RN 002 2022 7
18 0.0455655.C RN 002 2022 7
19 0.0455655.C RN 002 2022 7


CHill60 20-Jun-22 7:40am
   
Not simple at all. You have in your query
and rn='RN 002 2022'
but according to this data sample that should be
and id_fakture='RN 002 2022'

1 solution

Reading between the lines, I'm guessing you want something like this:
SQL
DELETE
FROM
    R
FROM
    dbo.trebovnica_roba_roba As R
WHERE
    id_fakture = 7
And
    rn = 'RN 002 2022'
And
    Exists
    (
        SELECT 1
        FROM dbo.trebovnica_roba_roba As R2
        WHERE R2.id_fakture = R.id_fakture
        And R2.rn = R.rn
        And R2.id < R.id
    )
;
That will delete all rows for the given id_fakture and rn other than the one with the lowest id.
   
v2
Comments
Goran Bibic 20-Jun-22 6:58am
   
YES and result need to be

13 DWL00446 RN 002 2022 7
14 0.0455655.C RN 002 2022 7

Other deleted, but result is 0 rows affected? Mising some detail
Why SELECT 1?
Richard Deeming 20-Jun-22 7:00am
   
The list of columns selected in an Exists / Not Exists query doesn't matter. You can use SELECT 1 FROM ..., SELECT * FROM ..., or SELECT id FROM ....

sql server - EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other? - Database Administrators Stack Exchange[^]
Richard Deeming 20-Jun-22 7:01am
   
That doesn't match the comment:
"How to remove all rows except first minimal id and sifra"
Goran Bibic 20-Jun-22 7:10am
   
How to remove all rows except first minimal id and sifra
This need to stay

13 DWL00446 RN 002 2022 7
14 0.0455655.C RN 002 2022 7

other rows to delete

id sifra id_fakture rn
13 DWL00446 RN 002 2022 7
14 0.0455655.C RN 002 2022 7
15 0.0455655.C RN 002 2022 7
16 DWL00446 RN 002 2022 7
17 DWL00446 RN 002 2022 7
18 0.0455655.C RN 002 2022 7
19 0.0455655.C RN 002 2022 7
Richard Deeming 20-Jun-22 7:16am
   
Not at all clear. The two rows you want to stay are also the two rows you want to delete?!
Goran Bibic 20-Jun-22 7:24am
   
I try to explane
id_fakure column ('RN 002 2022') and rn('7') are same for all items
Query need to delete all rows with same SIFRA and diferent ID exept firsT min(id)-NAME ROW
In this case minimal id for sifra DWL00446 is 13, for next sifra 0.0455655.C minimal id is 14
Other rows (becose have just 2 different sifra names in table) need to be deleted
Richard Deeming 20-Jun-22 7:31am
   
So:
DELETE
FROM
    R
FROM
    dbo.trebovnica_roba_roba As R
WHERE
    id_fakture = 7
And
    rn = 'RN 002 2022'
And
    Exists
    (
        SELECT 1
        FROM dbo.trebovnica_roba_roba As R2
        WHERE R2.id_fakture = R.id_fakture
        And R2.rn = R.rn
        And R2.sifra = R.sifra
        And R2.id < R.id
    )
;
then?
Goran Bibic 21-Jun-22 1:28am
   
THIS WORK. THANK YOU
Goran Bibic 20-Jun-22 7:27am
   
Need to delete all exception this condition

select MIN(id) as id, sifra from dbo.trebovnica_roba_roba where id_fakture=7 and rn='RN 002 2022' group by sifra
Goran Bibic 20-Jun-22 7:28am
   
This is ok condition
Richard Deeming 20-Jun-22 7:31am
   
No, because it doesn't work.
Goran Bibic 20-Jun-22 7:30am
   
I try this

delete from dbo.trebovnica_roba_roba
where NOT EXISTS (
select MIN(id), sifra
from dbo.trebovnica_roba_roba
where id_fakture=12 and rn='RN 002 2022'
group by sifra
)

O rws affected, nothing happend
Richard Deeming 20-Jun-22 7:31am
   
Ignoring my solution and trying the same thing that you tried in your question is not going to make any difference.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900