June 24, 2008 at 4:47 pm
Have orderrebate table.
ord_type ord_no item_no line_seq_no cd_tp
O 55 Part 1 1
O 55 Part 1 3
O 56 Part4 1 3
When the above records are inserted into orderrebate. After the insert I want to delete line 2. When distinct records come in based on the first 4 fields above I want to delete the on with cd_tp 3 and keep the one with cd_tp 1. If one record comes in with cd_tp 3 I want to keep it. I'm only deleting records with cd_tp 3 if they have a corresponding record with a cd_tp 1.
Nothing seems to work. This is my last hurdle in getting my program complete and I hitting a wall. Any help would be greatly appreciated.
Here is one of many examples I've tried with no luck:
delete OrderRebate
FROM OrderRebate
inner join inserted on inserted.ord_type = OrderRebate.ord_type and
inserted.ord_no = OrderRebate.ord_no and inserted.line_seq_no = OrderRebate.line_seq_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no
and inserted.line_seq_no = OrderRebate.line_seq_no and inserted.cd_tp = '3'
and exists
(
select ord_type, ord_no, item_no, line_seq_no
from OrderRebate
where
inserted.ord_type = ord_type and
inserted.ord_no = item_no and
inserted.ord_no = ord_no and inserted.line_seq_no = line_seq_no
group by ord_type, ord_no, item_no, line_seq_no
having count(*)>1
)
June 24, 2008 at 6:04 pm
Please read: ... http://www.sqlservercentral.com/articles/Best+Practices/61537/
Following the above best practices will help us help you.
This appears to be a cross posting of
http://www.sqlservercentral.com/Forums/Topic522558-145-1.aspx#bm522906
Is it?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply