Deleting Records

[Steve95]

Hi All

I have need to know of an effective way to delete 2000 records from a table holding 4000 records. Their is a unique key ie. originalno. which identifies each record and also I have the originalno. of all the records that need to be deleted.

But what syntax do I use to make this quick and correct rather than going through each originalno, and placing comers after each to extract the relevant records for deletion?

Many Thanks in advance.......

 

[ca8msm]

You could load the records to delete into a temp table and then join to the main table for the delete query.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Steve95]

But I still need to put comers at the end of each originalno. and I have around 200 records hence the reason Iam looking for a more effective syntax or way.

 

[monksnake]

What is a comer?

<.

 

[onpnt]

I'm kind of confused (an not going near the "comer")

Are you just looking for truncate being a faster way to delete?

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[monksnake]

I really don't see 2000 rows taking but a split second to delete

<.

 

[nice95gle]

I think me means a comma. If your originalno is a numernic field you would have to change the data type to a varchar to expect a comma. But if not you can follow the example below to delete and update the originalno field with a comma.

--table which hold all your data
Declare @MAINTBL TABLE(
originalno varchar(10) primary key
)

--list of originalno that needs to be deleted
Declare @TEMPTBL TABLE(
originalno varchar(10) primary key
)
insert into @MAINTBL
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6

insert into @TEMPTBL
select 4
union
select 6
union
select 1

--see data before delete 
select * from @MAINTBL

--delete unwanted data
DELETE @MAINTBL 
FROM @MAINTBL a
inner join @TEMPTBL b
on a.originalno=b.originalno

--Update originalno with a comma at the end
UPDATE @MAINTBL
SET originalno=originalno+','

--see data after delete 
select * from @MAINTBL

Well Done is better than well said
- Ben Franklin

 

[Steve95]

Hi All

thanks for the posts and yes it was a typo on my behalf, it suppose to be a comma.

The thing that still bothers me is that, my field originalno is a varchar in an non sequential format, so I need to extract these records but like I said I dont want to be putting ',' comma for 2000 records when deleting these.

As Iam new to SQL my assumption was to bring these records back and then just apply the drop (delete) statement to these records.

Currently Iam trying to understand and apply nice95gle statement to my code and see it impacts.

Many Thanks

 

[nice95gle]

Steve95

Sorry if I didn't make it clear. I'm just showing an example based on a table variable. Copy and paste the above code into query analyzer and run it to see how it works.

Just to break it down, I am creating two tables in menory, inserting some data and then using a INNER JOIN to get the records that need to be removed. Once that is done I am inserting a comma at the end of the left over originalno values.

Let me know if you need any additional help.

Good Luck





Well Done is better than well said
- Ben Franklin