Thanks alot Ken !

I understand the idea with joining through a truncate of the code, but how do I put my exsiting table with data in a @t Table in an easy...

I have a uniqe code like this...

BE31    Prov. Brabant Wallon

BE310  NULL               

BE32    Prov. Hainaut      

BE321  NULL               

BE322  NULL               

BE323  NULL               

BE324  NULL               

BE325  NULL               

BE326  NULL               

BE327  NULL               

BE33    Prov....

Hey Phill Carter,

Thanks, it just missed a littel "as b"

Delete from Mytab

from Mytab as a

    inner join (

      select Road, MainRoad

      from Mytab as b

      where b.Airport = 'A'

      group...

It's a very very long story. Sometime reality overseeds fantasy. The database I'm working with is violating everything, even the most basic database design rules.

And the worst thing is...

only the rows that are "duplicates" in three columns 

Thanks kgayda,

But it gives me,

Server: Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'as'.

And there is no way to do the query without a self join.

Regards...

The probelm is solved, allways remember to see if there is triggers on table

Regards Joejoe 

Thanks RGR'us

But when I run your script, the same thing happens

(1234564 row(s) affected)

But nothing is changed, it's all NULLs ???

1000 Thanks to all of you, my problem is solved

Joejoe  

Thanks GilaMonster this was precisly what I was looking for

I've tested it and its perfect.

But now I found out that ex. B1 or B88 also...

Hi Sushila,

I must have misunderstood the code, I thought that it cut away the first character, without knowing if it is A, B or G???

I have just tried it, it gives me...

Thanks to all of you

I'm lucky that the combination "only" is A34 or B01 or just 01, never A56B67 ect. but thanks alot Learn2Live!

I...

Hi sushila,

Thanks for your advice. There are all sorts of combinations, A1, A, 1, B99 ect. Finding what's not A or B is easy, but I would like to have an...

Thanks AJ,

I will try it out

You are right Remi and once again thanks for your help!