March 9, 2011 at 3:13 pm
In Order table, how to code to delete count(OrderID )>1 and [City]=null?
For example, second row should be deleted but third and fourth row need to keep.
OrderID-----City
111111-----New York
111111-----
222222-----London
333333-----
March 9, 2011 at 4:07 pm
Try this:
WITH cte(OrderID)
AS (SELECT OrderID
FROM [Order]
GROUP BY OrderID
HAVING COUNT(*) > 1
)
DELETE o
FROM [Order] o
JOIN cte ON o.OrderID = cte.OrderID
WHERE o.City IS NULL ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 9, 2011 at 4:26 pm
Are you looking for something like this:
create table tblOrders (OrderID int, City varchar(100))
go
insert into tblOrders values ('11111','New York')
insert into tblOrders values ('11111','')
insert into tblOrders values ('22222','London')
insert into tblOrders values ('33333','')
insert into tblOrders values ('33333','Paris')
go
;with CTE as
(select OrderID,City,ROW_NUMBER() OVER(PARTITION BY OrderID Order By City) as RowID
from tblOrders
)
delete from tblOrders
where OrderID in
(select distinct orderid
from CTE
where RowID > 1)
and City = ''
March 9, 2011 at 4:33 pm
Or this>
delete from #Temp
where OrderID IN
(
select T.OrderID
FROM #Temp t
GROUP BY T.OrderID
HAVING (COUNT(*) > 1 AND COUNT(T.OrderID) <> COUNT(T.City))
)
March 9, 2011 at 4:54 pm
ColdCoffee (3/9/2011)
Or this>
delete from #Tempwhere OrderID IN
(
select T.OrderID
FROM #Temp t
GROUP BY T.OrderID
HAVING (COUNT(*) > 1 AND COUNT(T.OrderID) <> COUNT(T.City))
)
This deletes both rows where OrderID = 111111 which is not what the OP wants. The technique costs less than my CTE solution it just needs a tweak:
delete from #Temp
where OrderID IN
(
select T.OrderID
FROM #Temp t
GROUP BY T.OrderID
HAVING (COUNT(*) > 1 AND COUNT(T.OrderID) <> COUNT(T.City))
)
AND City IS NULL
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 9, 2011 at 4:59 pm
This is the requirement right ??
adonetok (3/9/2011)
For example, second row should be deleted but third and fourth row need to keep.
So, it is correct to delete the rows with multiple orders but with a single(or many) NULL entried..
As OP's request was ambiguous, both urs and mine will work. Lets wait watch for the OP to chime in.
March 9, 2011 at 5:06 pm
You have a point...I read more into this part of the requirement
delete count(OrderID )>1 and [City]=null
meaning to me "only delete rows with a NULL City if there is another row with the same ID with a non-NULL city, otherwise keep the row with a NULL city".
i.e. delete the row where ID = 111111 and City is NULL and leave the other 111111...and leave the row where ID = 333333 and City is NULL
Let's see...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply