SQL loop delete - 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 loop delete

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-15 : 06:19:24

Hi i need a delete by id on a loop but not sure how?

DECLARE

@NEWIDFIELD INT

select @NEWIDFIELD = TI_ID
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

===LOOP THROUGH THE TI_ID FROM THE QUERY ABOVE=====

BEGIN

DELETE FROM TRANSIT...T_TBL_TRANSIT
WHERE TI_ID = @NEWIDFIELD

END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-15 : 06:24:08

Why Loop?

DELETE FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 06:25:31

[code]DECLARE @NEWIDFIELD INT

SELECT @NEWIDFIELD = 0

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @NEWIDFIELD = MAX(TI_ID)
FROM TRANSIT...T_TBL_TRANSIT
WHERE TI_Date < DATEADD(YEAR, -1, GETDATE())

DELETE
FROM TRANSIT...T_TBL_TRANSIT
WHERE TI_ID = @NEWIDFIELD
END[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 06:27:09

quote:
Originally posted by harsh_athalye

Why Loop?

Business rules?
Only if TI_ID has record(s) older than a year, delete ALL records for that TI_ID...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 06:29:17

I guess this would do the very same thing.

DELETE		x
FROM		TRANSIT...T_TBL_TRANSIT AS x
INNER JOIN	(
			SELECT		TI_ID
			FROM		TRANSIT...T_TBL_TRANSIT
			GROUP BY	TI_ID
			HAVING		MIN(TI_Date) < DATEADD(YEAR, -1, GETDATE())
		) AS y ON t.TI_ID = x.TI_ID

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-15 : 06:33:12

quote:
Originally posted by Peso

quote:
Originally posted by harsh_athalye

Why Loop?
Business rules?
Only if TI_ID has record(s) older than a year, delete ALL records for that TI_ID...

E 12°55'05.25"
N 56°04'39.16"

Yes, but what's the significance of TI_ID here. The only important condition for deletion is date condition, I believe. So what's the need to delete ID-wise?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-15 : 06:34:22

i cant use that because i am deleting from a ms access linked server and i get locking issues, so a loop i think is the only way??/

i have tried the below but i get this error:
Cannot execute the query "SELECT `Tbl1002`.`TI_ID` AS `Col1005` FROM `TEST` `Tbl1002` WHERE `Tbl1002`.`TI_Date`< ? " against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST".

is this because of the use of MAX? as its a MS ACCESS linked server?

DECLARE @NEWIDFIELD INT

SELECT @NEWIDFIELD = 0

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @NEWIDFIELD = MAX(TI_ID)
FROM TRANSIT...T_TBL_TRANSIT
WHERE TI_Date < DATEADD(YEAR, -1, GETDATE())

DELETE
FROM TRANSIT...T_TBL_TRANSIT
WHERE TI_ID = @NEWIDFIELD
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 06:37:15

[code]DECLARE @Sample TABLE (ID INT, Date DATETIME)

INSERT @Sample
SELECT 1, GETDATE() - 400 UNION ALL
SELECT 1, GETDATE() UNION ALL
SELECT 2, GETDATE() - 370 UNION ALL
SELECT 3, GETDATE() - 3

SELECT *
FROM @Sample[/code]Consider this setup.
ID 1 has records older than 1 year. Delete ALL records even if they are from yesterday.
ID 2 has records older than 1 year. Delete ALL records even if they are from yesterday.
ID 3 hasn't records older than 1 year. Do NOT delete any records

I believe OP do not think set-based. My suggestion 02/15/2008 : 06:29:17 will take care of what OP want in one set-based solution.
Many programmers who turn database developers still think row-wise (cursor)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 06:39:45

quote:
Originally posted by craigmacca

Is this because of the use of MAX? as its a MS ACCESS linked server?

It depends. Are your data STORED in MS Access, and you are trying to delete them from QE/SSMS?
Are you data STORED in MS SQL Server, and you are trying to delete from from MS Access?

DECLARE and WHILE keywords are MS SQL Server keywords, not MS Access.

Try my second suggestion (the one with INNER JOIN).

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-15 : 06:45:34

Hi yes i have data in a MS Access DB and i need to run query (which will become a scheduled job) to delete old data.

i have tried the inner join way aswell with the same error of

Cannot execute the query "SELECT `Col1036` FROM (SELECT `Tbl1004`.`TI_ID` AS `Col1036` FROM `TEST` `Tbl1004` WHERE `Tbl1004`.`TI_Date`< ? ) Qry1060 ORDER BY `Col1036` ASC" against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 06:47:20

Well... The query you are posting IS NOT the same I posted earlier.

If you have the data IN MS Access database AND you want to delete them from QE/SSMS, try

DELETE		x
FROM		TRANSIT...T_TBL_TRANSIT AS x
INNER JOIN	(
			SELECT		TI_ID
			FROM		TRANSIT...T_TBL_TRANSIT
			GROUP BY	TI_ID
			HAVING		MIN(TI_Date) < DATEADD(YEAR, -1, GETDATE())
		) AS y ON t.TI_ID = x.TI_ID

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-15 : 08:03:42

ok many thanks, i have the below code working now, but it is taking ages, because of the max(TI_ID) there are over half a million records so i think its taking a while to get the max each time.

is there a quicker way to maybe get the first record found rather than max?

DECLARE @NEWIDFIELD INT

SELECT @NEWIDFIELD = 0

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @NEWIDFIELD = max(TI_ID)
FROM TEST...TEST
WHERE
DATEDIFF(m, TI_Date, getdate())>12

DELETE
FROM TEST...TEST
WHERE TI_ID = @NEWIDFIELD
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 08:05:59

SELECT top 1 @NEWIDFIELD = TI_ID
FROM TEST...TEST
WHERE
DATEDIFF(m, TI_Date, getdate())>12

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources