SQL Delete Query - 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

SQL Delete Query

Author

Topic

AndrewA
Starting Member

8 Posts

Posted - 2010-07-08 : 16:15:27

I have a table that contains about 200 rows and looks pretty much like this sample:

ID DatePaid WeekNumber
-----------------------------------------
0132 01/04/2010 1 *delete*
0342 01/04/2010 1 *delete*
0233 02/04/2010 1
0231 07/04/2010 2 *delete*
0237 08/04/2010 2

What I need is a DELETE statement that gets rid of instances where there is more than one date per week number, keeping the row where the ID is the maximum number - for example a statement that will zap out the rows I have marked with *delete* - albeit to be executed against my much larger table.

I have spent several hours and searched many forums, articles and books but can't find anything to achieve what I need.

Any help would be much appreciated.

Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-08 : 16:56:25

try this:



create table andrewa
(id int
,datepaid smalldatetime
,weeknumber int
)

insert into andrewa
select 132, '2010/04/01', 1 union all
select 342, '2010/04/01', 1 union all
select 233, '2010/04/02', 1 union all
select 231, '2010/04/07', 2 union all
select 237, '2010/04/08', 2 union all
select 452, '2010/04/12', 3 union all
select 551, '2010/04/19', 4
--(7 row(s) affected)


create table temp
(datepaid smalldatetime
,weeknumber int)


		insert into temp

					select
					max(x.datepaid)
					,x.weeknumber
					from
					(
					select
					y.weeknumber
					,a.id
					,a.datepaid
					from
					(
					select count(*)-1 as rows_to_delete
					,weeknumber 
					from andrewa
					group by weeknumber
					having count(weeknumber) > 1
					) as y
					join andrewa as a
					on a.weeknumber = y.weeknumber

					) as x
					group by x.weeknumber


delete from andrewa
where exists(
	select a.id
	
	from temp as t
	right outer join andrewa as a
		on t.datepaid = a.datepaid and t.weeknumber = a.weeknumber
		where
		 exists (Select t2.weeknumber from temp as t2 where t2.weeknumber = a.weeknumber)
	and t.datepaid is  null
	and a.id = andrewa.id)

drop table temp

select * from andrewa

AndrewA
Starting Member

8 Posts

Posted - 2010-07-08 : 18:11:12

Thanks for the quick reply. This seems to work perfectly, but a couple of things...

Firstly, the table I want to delete from is already a temp table, so there's no need to move the data to another temp table - it can just be deleted from this one.

Secondly, my sample data was incorrect (sorry!). The sample data is:

ID int,
DatePaid date,
WeekUID int

ID DatePaid WeekUID
2457 2010-06-18 20100611 *delete*
2458 2010-06-18 20100611 *delete*
2459 2010-06-18 20100611 *delete*
2460 2010-06-18 20100611
2413 2010-06-11 20100610 *delete*
2414 2010-06-11 20100610
2383 2010-06-04 20100609 *delete*
2384 2010-06-04 20100609 *delete*
2385 2010-06-04 20100609
2348 2010-05-27 20100508 *delete*
2349 2010-05-27 20100508 *delete*
2350 2010-05-27 20100508

I've tried modifying your sample to delete the unwanted rows (as marked *delete* in the sample) but I can't get it to work, it still leaves all the rows in the table. Would you mind tweaking your sample, given the above amendments please?

Apologies for duff information the first time round.

Thanks again
Andrew.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-09 : 04:36:32

If you are using sql server 2005 or greater the general approach is to use a ROW_NUMBER method. Here is an example for your dataset. it's safe to run as it uses a table variable. You can customise it for your needs.


DECLARE @foo TABLE (
	[ID] INT PRIMARY KEY
	, [datePaid] DATETIME
	, [weekUID] CHAR(8)
	)

INSERT @foo ( [Id], [datePaid], [weekUID] )
      SELECT 2457,	'2010-06-18',	20100611 --delete
UNION SELECT 2458,	'2010-06-18',	20100611 --delete
UNION SELECT 2459,	'2010-06-18',	20100611 --delete
UNION SELECT 2460,	'2010-06-18',	20100611 
UNION SELECT 2413,	'2010-06-11',	20100610 --delete
UNION SELECT 2414,	'2010-06-11',	20100610
UNION SELECT 2383,	'2010-06-04',	20100609 --delete
UNION SELECT 2384,	'2010-06-04',	20100609 --delete
UNION SELECT 2385,	'2010-06-04',	20100609
UNION SELECT 2348,	'2010-05-27',	20100508 --delete
UNION SELECT 2349,	'2010-05-27',	20100508 --delete
UNION SELECT 2350,	'2010-05-27',	20100508


-- Raw dump
SELECT * FROM @foo

-- Delete the unwanted rows
DELETE f
FROM
	@foo f
	JOIN (
		SELECT
			ROW_NUMBER() OVER (PARTITION BY [datePaid] ORDER BY [ID] DESC) AS [rowPos]
			, [Id] AS [fooId]
		FROM
			@foo f
		)
		delList ON delList.[fooID] = f.[ID] AND delList.[rowPos] > 1

-- SHow what we have left
SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 05:14:47

This will be enough

DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY Weeknumber ORDER BY [datePaid] DESC, ID DESC) AS [rowPos]
		FROM	@foo
	) AS d
WHERE	[rowPos] > 1

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-09 : 05:49:58

quote:
Originally posted by Peso

This will be enough
DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Weeknumber ORDER BY [datePaid] DESC, ID DESC) AS [rowPos]
FROM @foo
) AS d
WHERE [rowPos] > 1

N 56°04'39.26"
E 12°55'05.63"

cheers Peso -- For some reason I have a little mental block that -- I generally add the join where it isn't required. Seems to only be on deletes.

Also -- your query is slightly wrong.

Ammended:


DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY [weekUID] ORDER BY [datePaid] DESC, ID DESC) AS [rowPos]
		FROM	@foo
	) AS f
WHERE	[rowPos] > 1

You were referencing alias f but calling it d... typo!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AndrewA
Starting Member

8 Posts

Posted - 2010-07-09 : 08:03:55

Excellent! Thanks all - this works perfectly.

However in light of the SQL you gave me, perhaps I don't need to ship data out to the temp table first?

All I need to do, basically is this:

Update tblAccounts set LastEntryOfWeek = True where XYZ

Where XYZ is the same criteria in the delete statement.

I've tried amending the DELETE FROM to UPDATE, but can't quite get the syntax correct - is this actually possible?

Never knew SQL was so flexible!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 09:03:34

[code]UPDATE f
SET f.LastEntryOfWeek = 1
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [weekUID] ORDER BY [datePaid] DESC, ID DESC) AS [rowPos],
LastEntryOfWeek
FROM @foo
) AS f
WHERE [rowPos] = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

AndrewA
Starting Member

8 Posts

Posted - 2010-07-09 : 09:49:08

That's fantastic, thank you.

I didn't know about the ROW_NUMBER method and I am loathe to use cursors.

I'm going to go through all my SP's now, and update them!

Thanks again,
Andrew.

AndrewA
Starting Member

8 Posts

Posted - 2010-07-09 : 09:52:09

Bit cheeky... but is it possible for me to use the ROW_NUMBER method to replace this rather cumbersome code?

It takes several seconds to run and I believe it could run much quicker using this new technique?

declare @Year char(4)
declare @Month varchar(2)
declare @WeekNum char(2)
declare @UID varchar(10)
declare @RowID as int
declare @Count as int

set @Count = (select MAX(id) from tblAccounts)
set @RowID = 0

while @RowID <= @Count

begin
select @Year = (select YEAR(DatePaid) from tblAccounts where ID = @RowID)
select @Month = (select Right('0' + Convert(VarChar(2), Month(DatePaid)), 2)
from tblAccounts where ID = @RowID)
select @WeekNum = (select Right('0' + Convert(VarChar(2), WeekNumber), 2)
from tblAccounts where ID = @RowID)
select @UID = @Year + @Month + @WeekNum
update tblAccounts set WeekUID = @UID where ID = @RowID and WeekUID is null
set @RowID = @RowID +1
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 11:58:54

What have you tried this far?

N 56°04'39.26"
E 12°55'05.63"

AndrewA
Starting Member

8 Posts

Posted - 2010-07-12 : 17:24:05

Sorry wrong code.

I want to make this more efficient:

--Insert relevant rows into temporary table
select id, WeekUID, WeeklyTotal into #tempAccounts from tblAccounts where LastEntryOfWeek = 1

declare @MaxID int
declare @Total numeric(10,2)
declare @Counter int
declare @ID int
declare @UID varchar(15)

--Loop through the temporary table one row at a time, calculating the weekly total figures
--then update the temp table accordingly
set @Counter = 1
select @MaxID = max(ID) from #tempAccounts
while @Counter <= @MaxID
begin
select @ID = (select ID from #tempAccounts (nolock) where ID = @Counter)
select @UID = (select WeekUID from #tempAccounts (nolock) where ID = @Counter)
select @Total = (select SUM(Amount) from tblAccounts where WeekUID = @UID)
update #tempAccounts set WeeklyTotal = @Total where ID = @Counter
set @Counter = @Counter + 1;
end

--Update the live table with the data from the temp table
update tblAccounts
set WeeklyTotal = #tempAccounts.WeeklyTotal
from #tempAccounts
where tblAccounts.ID = #tempAccounts.ID

--Dispose of the temp table
drop table #tempAccounts

This is what I have so far. It updates the right entries but inserts the total for the entire database rather than the sub total for the block of records that share a unique week id (WeekUID):

UPDATE f
SET f.WeeklyTotal = (select SUM(Amount) from tblAccounts where WeekUID = WeekUID)
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY [weekUID] ORDER BY [datePaid] DESC) AS [rowPos],
WeeklyTotal, WeekUID, Amount
FROM tblAccounts
) AS f
WHERE [rowPos] = 1

AndrewA
Starting Member

8 Posts

Posted - 2010-07-13 : 16:25:42

Anyone?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-14 : 06:22:26

it's not really about deleting any more so you should probably raise a new post.

Looks like a running total type problem. Peso ran a competition a while back about this very thing. Be prepared for some mind bending quirky update answers.

I'm not going to be able to help though. I'm going to be too busy for the next few days.

Good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources