Insert Statement where clause logic

[lsmyth1717]

I am trying to write some sql without using a cursor which looks at a row in a table and if the value exists in a row with a previous record no then i don't want to insert the record.

In the example below I would like to put another where clause into my insert statement which doesn't allow record no 5 to be inserted because the ToURN of 100 already exists as a FromURN in a previous record. Can anyone help me to add this logic in without having to use a cursor

Table: myTable
RecNo MergeFromURN MergeToURN MergeDateMerged
1 100 200 15/06/1982
2 200 300 15/06/1982
3 300 400 15/06/1982
4 500 600 15/06/1982
5 700 100 15/06/1982

INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED, RecNo)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged), Min(RecNo)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and
MergeToURN not in (select MoveToURN from Move)) and
--needs completed to help me do this
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN

 

[amrita418]

Add this code to your where clause for the desired output.

		and not exists (select 	'x' 
				from 	Move b
				where 	a.RecNo > b.RecNo
                          and  a.MergeToURN = b.MergeFromURN)

PS: You need to add an alias (a) to the MyTable after you plug this code in.

Regards,
AA

 

[lsmyth1717]

Not sure exactly what you meant by add an alias (a) to the MyTable after you plug the code in. So far I have the following can you show me what you mean. Cheers

INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED, RecNo)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged), Min(RecNo)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and
MergeToURN not in (select MoveToURN from Move)) and
and not exists (select 'x'
from Move b
where a.RecNo > b.RecNo
and a.MergeToURN = b.MergeFromURN)
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN

 

[amrita418]

Did you try this?

INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED, RecNo)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged), Min(RecNo)
from myTable [COLOR=red]a[/color]
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and 
MergeToURN not in (select MoveToURN from Move)) and
and not exists (select     'x' 
                from     Move b
                where     a.RecNo > b.RecNo
                          and  a.MergeToURN = b.MergeFromURN) 
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN

Regards,
AA