Delete statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete statement

Author

Topic

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-06-21 : 14:34:10

I have Table A (130 mill rows) and Table B (2.5mill rows). I want to delete rows in table B that exists in table A. Could some one please tell how I can do this?

Thanks

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-21 : 14:38:22

i would suggest inserting rows from tableb that don't exist in table a into tablec then dropping tableb and renaming tablec to tableb

how about that?

Duane.

Kristen
Test

22859 Posts

Posted - 2005-06-21 : 14:40:49

[code]
DELETE D
FROM TABLE_A
JOIN TABLE_B D
ON B_PK = A_PK
[/code]
If there are a lot of rows to delete then it might be more prudent to delete them in batches. Be aware that if you start a "big" deleted, find that it is taking too long and "abort" it then it is likely to take AT LEAST AS LONG AGAIN to rollback!
[code]
DECLARE @intRowCount int
SELECT @intRowCount = 1
SET ROWCOUNT 10000
WHILE @intRowCount > 0
BEGIN
... deleted statement ...
SELECT @intRowCount = @@ROWCOUNT
-- Possible "PAUSE" here to allow other processes to run
END
SET ROWCOUNT 0
[/code]
Kristen

Kristen
Test

22859 Posts

Posted - 2005-06-21 : 14:41:45

"how about that?"

Good plan, in my experience, unless:

o Indexes to migrate
o Foreign keys involved

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-21 : 14:45:47

quote:
Originally posted by Kristen

"how about that?"

Good plan, in my experience, unless:

o Indexes to migrate
o Foreign keys involved

Kristen

yeah.. thanks Kristen - i was supposed to mention that :)

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-21 : 15:04:36

Would you want to add a begin and commit to the loop?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-06-21 : 15:06:13

Thanks all for your inputs. Since my 2.8 is a temp table that I am using anyway and it does not have any indexes or foriegn keys to it. I was wondering that I will go by creating Table c and can you please tell me whether i should be using a select insert into ?

Can you please tell how the sql will be? Thanks much!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-21 : 15:15:12

2.8 million rows in a temp table? - that is not a good idea.

should rather be a physical (semi temporary) table

create tablec(coloumna int, columnb int)
go
insert into tablec
select a.columna, a.columnb
from tablea a
where not exists(select b.columna from tableb b where a.columna = b.columna and a.columnb = b.columnb)

thats maybe one way of doing it

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-21 : 15:16:09

I don't know..I mean yes you would Do that, but I'm not so sure that duplicating 128 million rows will be more effecient than doing the DELETE especially with the Loop and committed transactions....

Please post the DDL for your Tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-06-21 : 15:16:18

oh and the rename can be done by calling the stored proc sp_rename - you can look that up in books online :)

Duane.

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-06-21 : 15:31:15

Ditch, what I meant was that it is a physical table but I am using it only for this (temp) purpose. I had used that to extract data from Oracle and now trying to get the delta. The structure of this able is it table b is a 9 column table with 6 varchar(12) and 3 dateime columns with a primary key of a varchar(12) COLUMN.

Well about the X002548 note of duplicating 128 mill rows I am not sure how will I be doing that? can you please explain? thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-21 : 15:37:35

Do you how to script the table DDL in Enterprise Manager?

That would be very helpful to us.

Why don't you do

SELECT COUNT(*) FROM TABLEA a JOIN TABLEB b ON a.PK = b.PK

and tell us what that number is

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-06-22 : 06:58:58

"Would you want to add a begin and commit to the loop"

Shucks! Do you reckon that's needed Brett?

Will the whole loop otherwise just be a single commit block? I didn't think that was the case, and if it is I'm a dead man!!

Kristen

Kristen
Test

22859 Posts

Posted - 2005-06-22 : 07:01:51

insert into tablec
select a.columna, a.columnb
from tablea a
where not exists(select b.columna from tableb b where a.columna = b.columna and a.columnb = b.columnb)

I'd be inclined to use an OUTER JOIN, rather than an EXISTS, but I don;t have any knowledge that one is faster than the other.


insert into tablec
select a.columna, a.columnb
from tablea a
LEFT OUTER JOIN tableb b
     ON b.columna = a.columna
    AND b.columnb = a.columnb
WHERE b.columna IS NULL

Kristen

nssjari
Starting Member

46 Posts

Posted - 2005-06-22 : 07:33:34

Can you please help me solving a simple problem .... its very urgent
nssjari

quote:
Originally posted by Kristen

DELETE D
FROM TABLE_A
JOIN TABLE_B D
ON B_PK = A_PK

If there are a lot of rows to delete then it might be more prudent to delete them in batches. Be aware that if you start a "big" deleted, find that it is taking too long and "abort" it then it is likely to take AT LEAST AS LONG AGAIN to rollback!

DECLARE @intRowCount int
SELECT @intRowCount = 1
SET ROWCOUNT 10000
WHILE @intRowCount > 0
BEGIN
... deleted statement ...
SELECT @intRowCount = @@ROWCOUNT
-- Possible "PAUSE" here to allow other processes to run
END
SET ROWCOUNT 0

Kristen

Jari
Computer Engg

sujosh
Yak Posting Veteran

55 Posts

Posted - 2005-06-22 : 12:45:35

Thank you all! Brett, the ccount was 2.3 million and It wokred like a charm. Thank you Brett,Buane and Kristen.

Kristen
Test

22859 Posts

Posted - 2005-06-23 : 12:07:05

Brett: Can you comment on my "Shucks" bit above please? Thanks

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-23 : 12:27:25

from BOL:

quote:

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Microsoft® SQL Server™ operates in three transaction modes:

Autocommit transactions

Each individual statement is a transaction.

Explicit transactions

Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions

A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

as i understand this each delete is a transaction in it's own if not specified differently.

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-23 : 12:56:33

OK

I keep changing platforms like I change socks...I guess I'm always wrapping things in transaction...Oracle is 100% the other way


USE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myOrders99 FROM Orders
GO

DECLARE @month int
SELECT @Month = 1

WHILE @month < 13
  BEGIN
	IF @month <> 10
	  BEGIN
		DELETE FROM myOrders99 WHERE MONTH(OrderDate) = @month
		SELECT 'Rows Deleted: ' + CONVERT(varchar(3),@@ROWCOUNT)
		SELECT @month = @month + 1
	  END
	 ELSE
		DELETE FROM XXX
  END
GO

SELECT * FROM myOrders99 WHERE MONTH(OrderDate) < 10
GO

SET NOCOUNT OFF
DROP TABLE myOrders99
GO


USE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myOrders99 FROM Orders
GO

DECLARE @month int
SELECT @Month = 1
BEGIN TRAN
WHILE @month < 10
  BEGIN
	DELETE FROM myOrders99 WHERE MONTH(OrderDate) = @month
	SELECT 'Rows Deleted: ' + CONVERT(varchar(3),@@ROWCOUNT)
	SELECT @month = @month + 1
  END
	ROLLBACK TRAN
GO

SELECT * FROM myOrders99 WHERE MONTH(OrderDate) < 10
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 01:22:19

That's a relief! Thanks Brett.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources