Deleting from atable

Question

Post reply

Deleting from atable

Debbie Edwards

SSCrazy Eights

Points: 9217

January 30, 2007 at 4:31 am

#171950

Hello again,

Another delete query that Im struggling with... I have a table full of records with start and end dates. Some of them have different start but the same end dates so I created a temp table of these specific records.

SELECT PER,DFES, TO_DATE, COUNT (*) COUNT

INTO #ATTENDANCES_b1

FROM #ATTENDANCES_b

GROUP BY PER,DFES, TO_DATE

HAVING COUNT(*)>1

Below is an example of the results

PER	DFES	TO_DATE	COUNT
111	8888	31/08/2004	2
222	2344	18/07/2003	2

I then used this table to create another temp table consisting of all the data for each record where this is an issue:

SELECT b.*

INTO #ATTENDANCES_b2

FROM #ATTENDANCES_b b

INNER JOIN #ATTENDANCES_b1 b1

ON b.PER = b1.PER AND b.DFES = b1.DFES AND b.TO_DATE = b1.TO_DATE

GO

Again below is an example of some of the records brought back:

PER	UPN	TEMP_UPN	FNAME	MNAME	SNAME	DOB	DFES	SCH_NAME	FROM_DATE	TO_DATE	COUNT
111	95859		Mark	David	Holmes	27/08/1990	8888	Main Road School	01/01/2001	18/07/2003	1
111	95859		Mark	David	Holmes	27/08/1990	8888	Main Road School	07/09/1999	18/07/2003	1
222	51969		Tim	Stephen	Coles	05/01/1992	2344	Summer Lea School	05/09/2001	31/08/2004	1
222	51969		Tim	Stephen	Coles	05/01/1992	2344	Summer Lea School	01/04/2002	31/08/2004	1

What I want to do is delete from the original tmp table #ATTENDANCES_b the rows from this table with the minimum from date, leaving just one record.

Ive obviously gone about this the wrong way especially creating two temporary tables in order to delete from table b and Ive tried lots of ways but nothing is working, Im sure its quite easy. I had this extra bit of query

SELECT b2.PER_ID,b2.SCH_DFEE, MAX(b2.PAT_FROM_DATE) AS PAT_FROM_DATE

FROM #IDEAR_ATTENDANCES_b2 b2

GROUP BY b2.PER_ID, b2.SCH_DFEE

but Im still unsure how to use it within a delete.

Thanks for any help on this one, its really appreciated.

Debbie

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Debbie

I'm assuming that the combination of UPN, FROM_DATE and TO_DATE is unique.. if not then you'll have to add extra logic.

This is untested, since you didn't provide any DDL or sample data:

WITH

Dupes AS (

SELECT UPN, MIN(FROM_DATE), TO_DATE

FROM #Attendances_b

GROUP BY UPN, TO_DATE)

DELETE FROM a

WHERE NOT EXISTS

(SELECT * FROM Attendances_b a

JOIN Dupes d

ON a.UPN = d.UPN

AND a.FROM_DATE = d.FROM_DATE

AND a.TO_DATE = d.TO_DATE)

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Debbie Edwards

SSCrazy Eights

Points: 9217

January 30, 2007 at 7:35 am

#686446

Thanks for that. I will give it a go!

Debbie

Ian Yates SSCoach Points: 19738 More actions · Answer 3

You can also use this syntax (works with updates as well).

delete from A

from tableA A

inner join tableB B

on A.pkey = B.Akey

this lets you delete data from the table aliased as "A" in the from clause - in this case tableA. The deletion occurs for rows that satisfy the subsequent "select-style" part of the query - the 2nd from clause onwards. I haven't looked at your previous queries in detail but this should help you avoid the temporary tables.

The exists method, as suggested, should also work for you. However, when you find that you have to update the data in table A based on some data in table B then the above delete query (using an update instead obviously) is the way to go! Not often known as the syntax can look awkward but it's very handy.