Inner join update query - understanding issue

[jrsansom]

Afternoon,

I have a query regarding performing an update using an inner join. Below is an example data set. What I wish to achieve is for the code column to be updated consistently across all matching records.

ID	Code	CompanyGroup	Contact Group
1	A	12345	84566
2	C	12345	84566
3	A	12345	84566

The desired resulting codes would be:

AC
CA
AC

Can you recommend the best way to implement this. y current code is of the form:

update B
set code =dbo.getUniquecodeString(A.code,B.code)
from TableNAme A
	inner join TableNAme _pool B on
A.CompanyGroup=B.CompanyGroup and
A.contactGroup=B.ContactGroup
where A.ID <> B.ID

Also, this is more of an understanding thing buy why are only 3 updates performed when there are 6 pairings using a select statement based on the same join rule.

Many Thanks,
John

 

[bborissov]

update B
set code = dbo.getUniquecodeString(A.code,B.code)
from TableNAme A
    inner join TableNAme B on
          A.CompanyGroup =  B.CompanyGroup and
          A.contactGroup =  B.ContactGroup and
          A.ID           <> B.ID           and
          A.Code         <> B.Code

I don't know what getUniquecodeString() function does.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jrsansom]

Hi,

Thanks for your reply. The function takes two strings containing codes and returns the unique coded string.

So A and C become AC
A A become A
etc.

 

[bborissov]

Then you don;t need a function )

update B
set code = A.code+B.code
from TableNAme A
    inner join TableNAme B on
          A.CompanyGroup =  B.CompanyGroup and
          A.contactGroup =  B.ContactGroup and
          A.ID           <> B.ID           and
          A.Code         <> B.Code

just because of the last row of the join condition.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jrsansom]

Well that is not entirely true becuase AC and CA are the same two codes but different strings

 

[bborissov]

BUT your desired result was:

AC
CA
AC

Isn't it?
You never have rsocrds like:

AA
CC
BB
..

but you will have a records like you post

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP