On update from a table on a different database -Cannot resolve the collation conflict

Question

I am trying to update a table based on conditions from another table on a different database as follows:

 update Table1
    set Table1.Name=[release].[People].[Details].Name
    from Table1  inner join [release].[People].[Details]  on Table1.AccountNumber =[release].[People].[Details].AccountNumber 

But it throws an error:

"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

So I tried the following:

update Table1
    set Table1.Name=[release].[People].[Details].Name
    from Table1  inner join [release].[People].[Details]  on Table1.AccountNumber =[release].[People].[Details].AccountNumber 
where   Table1.AccountNumber COLLATE DATABASE_DEFAULT =[release].[People].[Details].AccountNumber  COLLATE DATABASE_DEFAULT

I have also tried:

update Table1
    set Table1.Name=[release].[People].[Details].Name
    from Table1  inner join [release].[People].[Details]  on Table1.AccountNumber =[release].[People].[Details].AccountNumber 
and  Table1.AccountNumber COLLATE DATABASE_DEFAULT =[release].[People].[Details].AccountNumber  COLLATE DATABASE_DEFAULT

and they don't work. I can not alter the table structure.

Answer 1

You are trying to compare two columns in the ON clause of your join which are from two different collations:

• Latin1_General_CI_AS
• SQL_Latin1_General_CP1_CI_AS

As a workaround, you may try casting one column to the other's collation:

UPDATE t1
SET Name = d.Name
FROM Table1 t1
INNER JOIN [release].[People].[Details] d
    ON t1.AccountNumber COLLATE SQL_Latin1_General_CP1_CI_AS =
       d.AccountNumber COLLATE SQL_Latin1_General_CP1_CI_AS;

Answer 2

Try to use like this once

update Table1
    set Table1.Name=[release].[People].[Details].Name
    from Table1  inner join [release].[People].[Details]  on Table1.AccountNumber COLLATE Latin1_General_CI_AS =[release].[People].[Details].AccountNumber COLLATE  Latin1_General_CI_AS 

or alter the cloumn by using below query

ALTER TABLE Table1
  ALTER COLUMN AccountNumber 
    int COLLATE Latin1_General_CI_AS NOT NULL

Answer 3

The collation is related to the JOIN. Please try

   update Table1
set Table1.Name=[release].[People].[Details].Name
from Table1  inner join [release].[People].[Details]  
     on Table1.AccountNumber = [release].[People].[Details].AccountNumber 
      COLLATE 'Latin1_General_CI_AS' 

Sometimes you'll need to use alias like this

 update Table1
 set Table1.Name=rel.Name
 from Table1  
  inner join [release].[People].[Details] rel 
      on Table1.AccountNumberCOLLATE 'Latin1_General_CI_AS'  =
         rel.AccountNumber COLLATE 'Latin1_General_CI_AS' 

Answer 4

Collations are linked to string data types and define how are they compared against other strings. As example Latin1_General_CI_AS, the last 4 characters mean

• CI: Case insensitive. Can be CS for sensitive.
• AS: Ascent sensitive. Can be I for insensitive.

Check the following examples:

• Comparing Cafe with Café using ascent insensitive returns results:

  DECLARE @Text1 VARCHAR(100) = 'Café' -- has ascent
  DECLARE @Text2 VARCHAR(100) = 'Cafe'
  
  SELECT 
      'match'
  WHERE 
      @Text1 COLLATE Latin1_General_CI_AI = 
      @Text2 COLLATE Latin1_General_CI_AI
  

• Doing the same with ascent sensitive doesn't return results:

  DECLARE @Text1 VARCHAR(100) = 'Café' -- has ascent
  DECLARE @Text2 VARCHAR(100) = 'Cafe'
  
  SELECT 
      'no match'
  WHERE 
      @Text1 COLLATE Latin1_General_CI_AS = 
      @Text2 COLLATE Latin1_General_CI_AS
  

• Using case insensitive will match when comparing lower to upper characters:

  DECLARE @Text1 VARCHAR(100) = 'STRONG weak'
  DECLARE @Text2 VARCHAR(100) = 'strong WEAK'
  
  SELECT 
      'match'
  WHERE 
      @Text1 COLLATE Latin1_General_CI_AI = 
      @Text2 COLLATE Latin1_General_CI_AI
  

• Collates also affects how records are grouped by (since they are compared against other rows):

  DECLARE @Table TABLE (String VARCHAR(100))
  INSERT INTO @Table (String) 
  VALUES ('Café'), ('Cafe') -- One with ascent, another without
  
  SELECT 
      String = T.String COLLATE Latin1_General_CI_AI,
      Rows = COUNT(1)
  FROM
      @Table AS T
  GROUP BY
      T.String COLLATE Latin1_General_CI_AI -- Ascent insensitive!
  
  /*
  Results:
      String  Rows
      Café    2
  */
  

• However, if the collation between each side of the comparison doesn't match, the engine won't risk to assume either one, so it throws an error:

  DECLARE @Text1 VARCHAR(100) = 'STRONG weak'
  DECLARE @Text2 VARCHAR(100) = 'strong WEAK'
  
  SELECT 
      'error!'
  WHERE 
      @Text1 COLLATE Latin1_General_CI_AI = 
      @Text2 COLLATE Latin1_General_CS_AI     -- Different collation
  

Msg 468, Level 16, State 9, Line 7 Cannot resolve the collation conflict between "Latin1_General_CS_AI" and "Latin1_General_CI_AI" in the equal to operation.

In your error, you have different character sets being compared (not the case or the ascent but the available language chars): Latin1_General_CI_AS Vs. SQL_Latin1_General_CP1_CI_AS.

You can solve this by explicitly casting either one to match the other. I don't know which is which, so the following solution will cast both to the same one:

update Table1 set 
    Name = [release].[People].[Details].Name
from 
    Table1
    inner join [release].[People].[Details] on 
        Table1.AccountNumber COLLATE Latin1_General_CI_AS = 
        [release].[People].[Details].AccountNumber COLLATE Latin1_General_CI_AS

Answer 5

Apply the COLLATE DATABASE_DEFAULT to the ON-Clause

update Table1
    set Table1.Name=[release].[People].[Details].Name
    from Table1 
    join [release].[People].[Details]  
        on Table1.AccountNumber = [release].[People].[Details].AccountNumber  COLLATE DATABASE_DEFAULT