How do I delete the corresponding row?
-
Oops! Sorry, I forgot. You have to specify a column name for a computed column is such query.
SELECT s.sickid
FROM sickness s
LEFT JOIN
(select payroll, staffid, start_date, end_date, min(sickid) as minsickid
from sickness
group by payroll, staffid, start_date, end_date) AS ok ON ok.minsickid = s.sickid
WHERE ok.minsickid IS NULL
Would this be better?
/edited - WHERE clause also needs to use the new alias for the column/
-
Hmm, I get "invalid column name 'sickid'"....
-
Please copy the whole SQL again from my previous post, I have edited it in several places. sickid was changed to minsickid in the JOIN "ON" clause and also in the WHERE clause.
If you still have problems, wait a minute till I create the tables here and some sample data, so that I can test it. Writing SQL out of thin air (like I did) is often a source of stupid errors. Sorry for that...
-
Ok, it works. I should have spotted that. Sorry.
Thanks, I'm gonna test the whole thing now!
-
Fine, now I have tested it and it works as I wanted it to (additionally, I noticed that payroll should not be considered when looking for duplicity, so this is changed here, too):
/*prepare environment*/
create table sickness (sickid int identity, payroll int, staffid int, start_date datetime, end_date datetime)
/*sample of data for testing*/
insert into sickness values (1, 1, '2002.01.01', '2002.01.21')
insert into sickness values (1, 1, '2002.01.01', '2002.01.21')
insert into sickness values (2, 1, '2002.01.01', '2002.01.21')
insert into sickness values (1, 1, '2002.03.01', '2002.03.12')
insert into sickness values (1, 9, '2002.01.01', '2002.01.21')
insert into sickness values (1, 7, '2002.01.01', '2002.01.21')
/*show table before delete*/
select * from sickness
IF object_id('tempdb..#del') IS NOT NULL DROP TABLE #del
/*locate records to be deleted - duplicity*/
SELECT s.sickid
INTO #del
FROM sickness s
LEFT JOIN
(select staffid, start_date, end_date, min(sickid) as minsickid
from sickness
group by staffid, start_date, end_date) AS ok ON ok.minsickid = s.sickid
WHERE ok.minsickid IS NULL
/*delete these records*/
DELETE s
FROM sickness s
JOIN #del ON s.sickid = #del.sickid
/*show table after delete*/
select * from sickness
I'm really sorry for the confusion I caused in the beginning, not having tested the SQL.
-
Thanks for that. I used a cursor to do the same job (also taking out payroll). I know cursors are not nice but how much difference is there between that and creating a temp table? I thoguht the reason cursors were considered inefficient was because they effectively create a temp table in memory. Is there any difference between the two methods?
-
The main difference between cursor and temp table is, that cursor is processed row by row... while SQL with temp table is processed at once, as a set. Set-oriented operations are one of the biggest advantages of SQL - performance is much much better than with cursors. However, you probably don't have to use temp table at all - if you decide to delete the corresponding rows by cascade delete. The reason I wrote it so, was is that you can use the same list of records to be deleted (#del) to perform several operations (deletes, checks, updates, whatever) over any number of related tables, that use the sickid as a foreign key. And also, because it illustrates how this approach works and what is happenning behind the scenes.
If you just want to delete all duplicate rows from sckness table, without storing anything for later use, simply do this:
DELETE s
FROM sickness s
LEFT JOIN
(select staffid, start_date, end_date, min(sickid) as minsickid
from sickness
group by staffid, start_date, end_date) AS ok ON ok.minsickid = s.sickid
WHERE ok.minsickid IS NULL
-
Ok, thanks for that. Also part of the reason I wanted to pursue the script option rather than cascade deletes is because it may be very useful for future reference and the more sql tips I can get the better (I'm actually a learner dba, not a programmer). So many thanks again!
-
Paula
I think you can re arrange your SP to produce a temp table with records marked to delete, instead of deleting them directly (changinf DELETE... for a INSERT INTO #TempTable SELECT...)
Once you have this temp table, you can delete depending records in any other table simply using a join... after that, you delete main records, using temp table as a deleting list.
I don't extend it with examples 'cause I'm short of time... if you need further explanations just respond...
hope it help!
Nicolas Donadio
SW Developer
-
Paula this is a little off the subject and perhaps I should start another thread but just a quick question on your statement "As a rule we do not use cascading deletes as we leave it up to the programmers to decide" I agree that using cascading deletes should not be used in all cases but when the logic (as seems to be the case here) of the relationship is such that cascading deletes makes sense it reduces the programing load. Do it right once (meaning setting the relationship) and forget it.
Attempting to delete a record that has a related record in another table will generate an error if cascading deletes are not allowed.
Of course it could be that I am missing something here.
Mike
-
Mike,
I realised yesterday that I should be getting an error if cascade delete is not on, but I am not. I'm not sure why. The child record does not get deleted but the parent does, and no error messages! Worrying. Half of our problem is that the DBA sets up the database but the programmer goes ahead and sets up the tables. They don't always have the knowledge of SQL Server to make the decisions like cascade deletes etc. But I will pay more attention to it from now on.
-
It sounds like the first step in lessening your grief and preventing(?) gray hair, is that you step in and remove the ability to add/modify tables from the programmers without going through the DBA.
Changing the model is not programmer work - it's pure DBA responsibility. (and it's not a thing to be taken lightly either - messing with the model is like fooling around with the lower bricks your house is built on)
About you not getting any error when deleting a parent that has children.. (which in itself technically isn't an error anyway) You probably have no FK on the child refererencing the parent. Add one and you won't risk creating orphans.
/Kenneth
-
Paula,
most probably your programmers did not enforce referential integrity through the database itself. It is quite common that programmers ensure referential integrity on the application (user interface) level, but in such case if you write a script to modify the data, you have to remember to take care of it as well.
-
Try this:
DELETE FROM sicknessdays
FROM sicknessdays
INNER JOIN(SELECT todel.sickid, dupe.maxsickid
FROM sickness AS todel
INNER JOIN (SELECT COUNT(1), payroll, staff_id, start_date
, end_date, MAX(sickid) AS maxsickid
FROM sickness
GROUP BY payroll, staff_id, start_date, end_date
HAVING count(1)>1) AS dupe
ON todel.payroll = dupe.payroll
AND todel.staff_id = dupe.staff_id
AND todel.start_date = dupe.start_date
AND todel.end_date = dupe.end_date
GROUP BY todel.sickid) AS delete
ON sicknessdays.sickid = delete.sickid AND sicknessdays.sickid < delete.maxsickid
DELETE FROM sickness
FROM sickness
INNER JOIN(SELECT todel.sickid, dupe.maxsickid
FROM sickness AS todel
INNER JOIN (SELECT COUNT(1), payroll, staff_id, start_date
, end_date, MAX(sickid) AS maxsickid
FROM sickness
GROUP BY payroll, staff_id, start_date, end_date
HAVING count(1)>1) AS dupe
ON todel.payroll = dupe.payroll
AND todel.staff_id = dupe.staff_id
AND todel.start_date = dupe.start_date
AND todel.end_date = dupe.end_date
GROUP BY todel.sickid) AS delete
ON sickness.sickid = delete.sickid AND sickness.sickid < delete.maxsickid
Andy
-
Hi I have a question on finding mutually exclusive events. Which is an offspring of this discussion
Valadan’s suggestion of using:
Will delete all records that are duplicates. But does not address the issue of an employee having claimed a sickness that is not mutually exclusive of all other sickness. How can this statement be modified to also delete records where the start and end dates of a sickness are contained within the start and end dates of another claimed sickness by the same employee.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply