Delete with condition

Question

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 19-Jun-22 23:46pm

Goran Bibic

Updated 20-Jun-22 0:49am

Add a Solution

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

Add a Solution

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

Richard Deeming · Answer 1 · 2022-06-20T00:49:00

Solution 1

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.

Posted 20-Jun-22 0:49am

Richard Deeming

Updated 20-Jun-22 1:49am

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.

Delete with condition

1 solution

Solution 1

Add your solution here

Preview 0

Delete with condition

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us