Difficult Insert where clause.

Sorry for anyone who has seen this query and dataset before but this is a
seperate question/issue which I am working on.
I am struggling trying to get my insert statement not to insert record 5
because the ToURN has been used before in a previous record(1).
Basically I am trying to write some logic which says that if the ToURN in
one record already exists in the FromURN field of a previous record then do
not insert the record.
Does anyone know how I would write the sql to do this.
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
6 100 100 15/06/1982
7 NULL 100 15/06/1982
8 700 0 15/06/1982
So far I have the following sql but need to go that step further to stop
record 5 being inserted because 100 already has been inserted as a from urn
in record 1.
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
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))
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN
while @.@.ROWCOUNT > 0
begin
update A set MoveToURN = B.MoveToURN
from Move A
inner join Move B on A.MoveToURN=B.MoveFromURN
end
Can anyone help me with this.Stephen
You are posted this question a few times some time ago , so people gave you
solutuion (me include), so would you mind at least posting DDL+ sample data
+ expected result
CREATE TABLE #Test
(
RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
f INT ,
t INT ,
dt DATETIME NOT NULL
)
INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
GO
INSERT INTO YourTable <column lists>
SELECT f, t , dt FROM #Test
WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
> Sorry for anyone who has seen this query and dataset before but this is a
> seperate question/issue which I am working on.
> I am struggling trying to get my insert statement not to insert record 5
> because the ToURN has been used before in a previous record(1).
> Basically I am trying to write some logic which says that if the ToURN in
> one record already exists in the FromURN field of a previous record then
> do
> not insert the record.
> Does anyone know how I would write the sql to do this.
> 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
> 6 100 100 15/06/1982
> 7 NULL 100 15/06/1982
> 8 700 0 15/06/1982
> So far I have the following sql but need to go that step further to stop
> record 5 being inserted because 100 already has been inserted as a from
> urn
> in record 1.
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
> 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))
> GROUP BY MergeFromURN, MergeToURN
> Order by MergeFromURN
> while @.@.ROWCOUNT > 0
>
----
--

>
> Can anyone help me with this.
>|||Sorry but this doesn't work as I need.
It inserts 5 rows into the table including the row. 700, 100 (5th Record).
I want to write in further logic which wouldn'd allow this record to be
inserted on the basis that the ToURN value of 100 already exists in a prior
record in the FromURN column.
Its quite hard to explain and I'm sorry for the previous posts which may be
covering the same ground.
"Uri Dimant" wrote:

> Stephen
> You are posted this question a few times some time ago , so people gave yo
u
> solutuion (me include), so would you mind at least posting DDL+ sample dat
a
> + expected result
>
> CREATE TABLE #Test
> (
> RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> f INT ,
> t INT ,
> dt DATETIME NOT NULL
> )
> INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
> GO
> INSERT INTO YourTable <column lists>
> SELECT f, t , dt FROM #Test
> WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
>
> ----
--
>
>
>|||Hi
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
No it does not , look at all columns and check it out
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
> Sorry but this doesn't work as I need.
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
> I want to write in further logic which wouldn'd allow this record to be
> inserted on the basis that the ToURN value of 100 already exists in a
> prior
> record in the FromURN column.
> Its quite hard to explain and I'm sorry for the previous posts which may
> be
> covering the same ground.
> "Uri Dimant" wrote:
>|||Honestly I ran it there now and it inserts 5 rows even though I don;t want i
t
to insert 700,100. Was trying to get around things without using a cursor
but i can't seem to find a way of doing this without using a cursor.
"Uri Dimant" wrote:

> Hi
> No it does not , look at all columns and check it out
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
>
>|||Hi
When I ran it I also go trow 5 in the result set.
This may have something to do with the order in which records are processed.
As in the options on your SQL server setup may be different to the person
who provided the solution, thus when you are running the select to insert ro
w
5 row 1 isn't necessarily in yet?
Just a guess
--
Chan
Programmer
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
select DISTINCT t1.f, t1.t, t1.dt
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo and
t1.f != t2.t
AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
It returns 4 rows
f t dt
100 200 2005-07-27 07:26:33.490
200 300 2005-07-27 07:26:40.897
300 400 2005-07-27 07:26:48.523
500 600 2005-07-27 07:26:54.523
Is this what you are looking for? You should avoid cursors whenever possible
.
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
SKIN that one up and smoke it!!
"frank chang" wrote:
> I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
> select DISTINCT t1.f, t1.t, t1.dt
> from #Test t1
> inner join #Test t2 on t1.RecNo < t2.RecNo and
> t1.f != t2.t
> AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
>
> It returns 4 rows
> f t dt
> 100 200 2005-07-27 07:26:33.490
> 200 300 2005-07-27 07:26:40.897
> 300 400 2005-07-27 07:26:48.523
> 500 600 2005-07-27 07:26:54.523
> Is this what you are looking for? You should avoid cursors whenever possib
le.
>
> "Stephen" wrote:
>|||Stephen, This select statement is the most appropriate one:
INSERT INTO ......
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS -- like MINUS operator in ORACLE, subtract the ones you don't wa
nt
(select *
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo
and t1.f != t2.t AND t2.f != t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
AND t1.RecNo = m.RecNo)
Sorry about that. I didn't drink coffee this morning.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>|||Stephen, The previous SQL statment had a typo in it (I cut and pasted by
mistake)
This statement may help:
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS // subtract set where from-urn number is already used
(select *
from #Test t1
inner join #Test t2 on t1.RecNo > t2.RecNo
and t2.f = t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t1.f)
AND t1.RecNo = m.RecNo)
Thnak you for your help.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>