Deleting from atable
-
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
-
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
-
Thanks for that. I will give it a go!
Debbie
-
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.
-
Excellent,
This might be better for me. I will try and get this into my query and let you know how it goes.
Debbie
-
I have used that but Ive deleted all the 10 records rather than just the 5 records where the FROM date is earlier than the other from date.
Whoops, Ill have to try this one again
Debbie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply