Delete Script. - 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

General SQL Server Forums

New to SQL Server Programming

Delete Script.

Author

Topic

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-16 : 03:57:19

Hi,

I have a table containing a list of pbxid's, called tblPBXIDTemp.

DDL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPBXIDTemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPBXIDTemp]
GO

CREATE TABLE [dbo].[tblPBXIDTemp] (
[PBXID] [int] NULL
) ON [PRIMARY]
GO

DML:

INSERT INTO tblPBXIDTemp(pbxID)
SELECT 543 UNION ALL
SELECT 10961 UNION ALL
SELECT 1012 UNION ALL
SELECT 83

and a DialupLog table which holds all dialups for pbx's (therfore has more than one entry for a pbxid).

DDL:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDialupLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDialupLog]
GO

CREATE TABLE [dbo].[tblDialupLog] (
[RegionalID] [int] NOT NULL ,
[PBXID] [int] NULL ,
[DialupDT] [datetime] NULL ,
[DongleAccessNum] [varchar] (64) NULL ,
[CLI] [varchar] (64) NULL ,
[RegionalDialup] [varchar] (50) NULL
) ON [PRIMARY]
GO

DML:

INSERT INTO tblDialupLog(PBXID,DialupDT ,DongleAccessNum,CLI,RegionalID,RegionalDialup )
SELECT 83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554' UNION ALL
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2006 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800003554' UNION ALL
SELECT 543,'8/8/2007 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2007 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'

Expected Result after i run the script, and on completion, do a Select * from tblDialupLog:

83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554'
1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800004249'
543,'8/8/2007 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554'
1012, '8/8/2007 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554'
16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'

My script needs to delete all entries from tblDialupLog where the pbxid is in tblPBXIDTemp except the TOP 1
(latest dialup according to DialupDT) entry for that particular PBXID.

Any Ideas would be great?
Thanks;o)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 04:06:35

Something like this?

Delete dl
from tblDialupLog dl join tblPBXIDTemp t1
on dl.pbxID = t1.pbxID
Join 
(
	Select PbxID, max(DialupDt) as DialupDt
	from tblDialupLog
	Group by PbxID
) as t2
on dl.pbxid = t2.pbxid and dl.dialupdt < t2.dialupdt

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 04:16:32

[code]SELECT DISTINCT x.PBXID,
x.MaxDT AS Dialup_DT,
l.DongleAccessNum,
l.CLI,
l.RegionalID,
l.RegionalDialup
INTO #Temp
FROM (
SELECT l.PBXID,
MAX(DialupDT) AS MaxDT
FROM tblDialupLog AS l
INNER JOIN tblPBXIDTemp AS t ON t.pbxID = l.PBXID
GROUP BY l.PBXID
) AS x
INNER JOIN tblDialupLog AS l ON l.pbxID = x.PBXID AND l.Dialup_DT = x.MaxDT

DELETE l
FROM tblDialupLog AS l
INNER JOIN tblPBXIDTemp AS t ON t.pbxID = l.PBXID

INSERT tblDialupLog
(
PBXID,
DialupDT,
DongleAccessNum,
CLI,
RegionalID,
RegionalDialup
)
SELECT PBXID,
DialupDT,
DongleAccessNum,
CLI,
RegionalID,
RegionalDialup
FROM #Temp

DROP TABLE #Temp[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 04:19:06

Harsh, there are two of these
SELECT 1012, '8/8/2007 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1012, '8/8/2007 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL

and two of these
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 543,'8/8/2007 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 04:27:49

quote:
Originally posted by Peso

Harsh, there are two of these
SELECT 1012, '8/8/2007 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1012, '8/8/2007 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL

and two of these
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 543,'8/8/2007 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL

Peter Larsson
Helsingborg, Sweden

No, Peter.
Both dates are for different years. One is for 2006 and one is for 2007.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 04:29:30

Great spot!

Are you using SQL Server 2005?

DELETE		t
FROM		(
			SELECT		pbxID,
					ROW_NUMBER() OVER (PARTITION BY pbxID ORDER BY dialupDT DESC) AS RecID
			FROM		tblDialupLog
		) AS t
INNER JOIN	tblPBXIDTemp AS l ON l.pbxID = t.pbxID
WHERE		t.RecID > 1

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-16 : 04:56:35

Thank you so much to both of you for your help... and no Peter, this is going to be run on sql server 7, it is a very old system.
Harsh your query works wonderfully...

Kind Regards,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 05:35:50

Yes, and it is fast too with a large number of records.
However, it don't handle possible duplicate records over pbxID and dialupDT combination.

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-16 : 05:44:34

Hi Peter,

In my table, there will never be duplicate records over a pbxID and dialupDT combination, so i don't need to worry about that.

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 05:46:44

quote:
Originally posted by Peso

Yes, and it is fast too with a large number of records.
However, it don't handle possible duplicate records over pbxID and dialupDT combination.

Peter Larsson
Helsingborg, Sweden

That was not in the original requirements specs !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 05:48:52

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-17 : 03:52:37

i need to make a bit of a modifictaion to Harsh's query above.

i now have another table called tblPBX

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPBX]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPBX]
GO

CREATE TABLE [dbo].[tblPBX] (
[PBXID] [int] NOT NULL ,
[RegionalID] [int] NOT NULL ,
[CurrentPBXMode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO tblPBX(PBXID,RegionalID, CurrentPBXMode )
SELECT 83, 0 , '1' UNION ALL
SELECT 543, 0 ,'0' UNION ALL
SELECT 1012, 0 ,'1' UNION ALL
SELECT 1219,0,'1' UNION ALL
SELECT 16499,0, '0'

Take note of the CurrentPBXMode Column, 0's represent commissioned sites.

What my query now has to do is delete all entries from tblDialupLog where the pbxid is in tbPBXIDTemp except if
tblPBX.CurrentPBXMode = 0 (for that particular pbxid), further more,if tblPBX.CurrentPBXMode = 0 then only the latest
entry (according to tbldialupLog.DialupDT) must be kept.

So my new expected result would be:

83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554'
1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800004249'
543,'8/8/2007 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554'
16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'

N.B. both entries for pbxid 1012 are deleted because it's currentPBXMode <> 0. and only the top
entry for 543 is left behind.
pbxid 16499 CurrentPBXMode = 0, but it does not exist in tblPBXIDTemp.

Regards,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 04:30:59

Do you really expect us to do your work for you, twice, for free?
Next time, please post ALL your problems at once.

You can easily solve your problem with a LEFT JOIN to the new table.

Peter Larsson
Helsingborg, Sweden

SQLJunior
Starting Member

34 Posts

Posted - 2007-04-18 : 04:52:50

Yeah well thanks for that then.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 04:57:29

You're welcome. And good luck.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources