Difficult row deletion question / data integrity

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)

Difficult row deletion question / data integrity

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-15 : 01:46:58

I have rows that are blank that need to be deleted. However they are only to be deleted if they are extraneous entries.

For example if you check out rows 1 and 4 they actually are related to rows 2,3 and 5,6 respectively (via shipdate, shipname, and executiontime) but is a dupe with no numbers for any of the prices. So they must be deleted.

However, rows 10 and 7 must not be deleted because they are not extraneous, they are unique, they are the only rows with that particular executiontime, shipname, and saildate

I'm not sure how I could do this with sql if it is possible, if anyone could help that would be greatly appreciated.



create table testing
(shipname nvarchar(100) null,
 saildate smalldatetime null,
itinerary nvarchar(4000) null,
interiorprice nvarchar(4000) null,
oceanviewprice nvarchar(4000) null,
balconyprice nvarchar(4000) null,
executiontime varchar(30),
ratetype nvarchar(50) null,
suiteprice nvarchar(4000) null,
id_num int not null)
go


insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('FA','11/1/2008 0:00','5 Day WESTERN  from $299 *','','','','8/12/2008','','','1');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('FA','11/1/2008 0:00','5 Day WESTERN  from $299 *','399','Sold Out','Sold Out','8/12/2008','Super Deal','799','2');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('FA','11/1/2008 0:00','5 Day WESTERN  from $299 *','399','Sold Out','Sold Out','8/12/2008',' Assigned Room','N/A','3');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('FA','12/13/2008 0:00','5 Day WESTERN  from $299 *','','','','8/12/2008','','','4');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('FA','12/13/2008 0:00','5 Day WESTERN  from $299 *','349','399','Sold Out','8/12/2008',' Assigned Room','N/A','5');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('FA','12/13/2008 0:00','5 Day WESTERN  from $299 *','389','399','Sold Out','8/12/2008','Super Deal','899','6');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('LI','8/16/2008 0:00','7 Day WESTERN  from $449 *','','','','8/12/2008','','','7');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('VI','1/18/2009 0:00','7 Day SOUTHERN  from $449 *','649','749','999','8/12/2008','Super Deal','1499','8');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('VI','1/18/2009 0:00','7 Day SOUTHERN  from $449 *','649','749','949','8/12/2008',' Assigned Room','N/A','9');
insert into testing(shipname, saildate, itinerary, interiorprice, oceanviewprice, balconyprice, executiontime, ratetype, suiteprice, id_num) values('LI','8/16/2008 0:00','7 Day WESTERN  from $449 *','','','','8/14/2008','','','10');

go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 02:01:00

[code]DELETE t
FROM YourTable t
INNER JOIN (SELECT shipdate, shipname, executiontime
FROM YourTable
GROUP BY shipdate, shipname, executiontime
HAVING COUNT(id_num) >1) t1
ON t1.shipdate=t.shipdate
AND t1.shipname=t.shipname
AND t1.executiontime=t.executiontime
WHERE t.interorprice=' '
AND t.oceanviewprice=' '
AND t.balconyprice=' '
AND t.suiteprice=' '[/code]

make usre you first put select instead of DELETE check if they are pulling the expected records before deleting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources