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

SQL Server 2005 Forums

Transact-SQL (2005)

Delete Query

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-18 : 05:18:12

Hi

You guys can probably solve this in a second, but I'm having problems trying to delete records from a table using this script:


DELETE FROM a
SELECT * FROM ACCTS a
INNER JOIN 
(
	SELECT ACCOUNT,  MIN(convert(varchar,convert(datetime,EFFECTIVE_DATE),103)) as EFFECTIVE_DATE
	FROM ACCTS
	WHERE ACCOUNT IN 
	(
		SELECT ACCOUNT
		FROM ACCTS
		GROUP BY ACCOUNT
		HAVING COUNT(*) > 1
	)
	GROUP BY ACCOUNT
) b
ON a.ACCOUNT = b.ACCOUNT
and convert(varchar,convert(datetime,a.EFFECTIVE_DATE),103) = b.EFFECTIVE_DATE

Basically I need to delete these records from ACCTs, but my delete code is incorrect.

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 05:26:25

Try

DELETE a FROM 
ACCTS a
INNER JOIN 
(
	SELECT ACCOUNT,  MIN(convert(varchar,convert(datetime,EFFECTIVE_DATE),103)) as EFFECTIVE_DATE
	FROM ACCTS
	WHERE ACCOUNT IN 
	(
		SELECT ACCOUNT
		FROM ACCTS
		GROUP BY ACCOUNT
		HAVING COUNT(*) > 1
	)
	GROUP BY ACCOUNT
) b
ON a.ACCOUNT = b.ACCOUNT
and convert(varchar,convert(datetime,a.EFFECTIVE_DATE),103) = b.EFFECTIVE_DATE
ORDER By a.ACCOUNT

Madhivanan

Failing to plan is Planning to fail

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-18 : 05:38:03

Excellent. Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 06:12:06

[code]DELETE x
FROM ACCTS
INNER JOIN (
SELECT ACCOUNT,
MIN(DATEADD(DAY, DATEDIFF(DAY, '19000101', EFFECTIVE_DATE), '19000101')) AS minDate
FROM ACCTS
GROUP BY ACCOUNT
HAVING COUNT(*) > 1
) AS y ON y.ACCOUNT = x.ACCOUNT
WHERE x.EFFECTIVE_DATE >= y.minDate
AND x.EFFECTIVE_DATE < DATEADD(DAY, 1, y.minDate)[/code]

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