August 28, 2012 at 5:26 am
Hi all!
This select statement:
select BOE_BOERNID, boe_fornavn from dbo.boern where boe_institution = 'Fritidsklub 4/5 kl.' order by BOE_INSTITUTION,BOE_FORNAVN,BOE_BOERNID
This gives me the records in the order i want them - There is 161 records.
somewhere down the line, i find boe_Bornid 1539,1282,1428,1682....
I want to delete the records in the above set from boe_boernid no 1682 until the end.
How to?
Every time i try to put something with an order by in a where clause, i get an error message.
Pls. advice
Best regards
Edvard Korsbæk
August 28, 2012 at 5:32 am
Try this:
;WITH CTE AS
(
select TOP 1000000 BOE_BOERNID,
boe_fornavn
from dbo.boern
where boe_institution = 'Fritidsklub 4/5 kl.'
order by BOE_INSTITUTION,BOE_FORNAVN,BOE_BOERNID
)
DELETE CTE
WHERE boe_Boernid >= 1682;
deleting from the CTE deletes from the underlying table...
August 28, 2012 at 5:44 am
OK - I didn't notice the BOE_BOERNIDs were out of order. This should handle it:
drop table dbo.boern
create table dbo.boern
(
BOE_BOERNID int,
boe_fornavn varchar(10),
boe_institution varchar(30)
);
insert into dbo.boern values ( 1539, 'AAAAAA', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1282, 'AAAAAB', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1428, 'AAAAAC', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1682, 'AAAAAD', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1700, 'AAAAAE', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1400, 'AAAAAF', 'Fritidsklub 4/5 kl.' );
;WITH CTE AS
(
select
ROW_NUMBER() OVER (ORDER BY BOE_INSTITUTION,BOE_FORNAVN,BOE_BOERNID) as rn,
BOE_BOERNID,
boe_fornavn
from dbo.boern
where boe_institution = 'Fritidsklub 4/5 kl.'
)
DELETE CTE
WHERE rn >= (select rn from CTE where BOE_BOERNID = 1682);
select * from dbo.boern ;
August 28, 2012 at 6:04 am
Thanks!
But:
In my DB, i have app. 6000 boern (Kindergarten children), and the lines:
drop table dbo.boern
create table dbo.boern
(
BOE_BOERNID int,
boe_fornavn varchar(10),
boe_institution varchar(30)
);
insert into dbo.boern values ( 1539, 'AAAAAA', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1282, 'AAAAAB', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1428, 'AAAAAC', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1682, 'AAAAAD', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1700, 'AAAAAE', 'Fritidsklub 4/5 kl.' );
insert into dbo.boern values ( 1400, 'AAAAAF', 'Fritidsklub 4/5 kl.' );
is a real slaughter on the kids :-)!
I had a set of records (161), and i wqnted to delete the last (161-43) records in that set - nothing more,
Best regards
Edvard Korsbæk
August 28, 2012 at 6:10 am
Hi
Creating & deleting the table is just to set up the test data.
You would only run the WITH CTE AS... statement.
The select is to look at the results.
It's just to show what data I used & so anyone interested can run it.
So this is the bit you would use:
;WITH CTE AS
(
select
ROW_NUMBER() OVER (ORDER BY BOE_INSTITUTION, BOE_FORNAVN, BOE_BOERNID) as rn,
BOE_BOERNID,
boe_fornavn
from dbo.boern
where boe_institution = 'Fritidsklub 4/5 kl.'
)
DELETE CTE
WHERE rn >= (select rn from CTE where BOE_BOERNID = 1682);
Hope that's clear!
Even though it says 'DELETE CTE' it really deletes from the underlying table dbo.Boern.
August 28, 2012 at 6:52 am
Thanks - Worked like a charm!
And i got some new understanding:
ROWNUMBER
OVER
Was new to me.
Humble regards
Edvard Korsbæk
August 28, 2012 at 7:20 am
No problem.
I learned something too:
I'll put proper notes in the script to explain what I'm doing next time!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply