SQL, Update a table with Cursor-CodePudding

I built a cursor to read records from the People table. I would like to compare the existing email address in the contacts table (saved under info) with the email addresses in the people table and create a person reference(refperson in the contacts table) based on the email address from Contacts.

In the person table are 3 different email addresses possible: eMail, Email2, Email3.

The update only occurs if I compare the email address from the contacts table with one value from the people table(not all 3 emails). I tried to combine the 3 conditions in a where clause, but the references are still not created.

Where is the mistake?

DECLARE @id uniqueidentifier, @email nvarchar(max), @email2 nvarchar(max),  @email3 nvarchar(max), @reffirma uniqueidentifier;
  
DECLARE cur_refperson CURSOR FOR   
select p.ID, p.eMail,p.Email2, p.Email3,p.RefFirma
from Personen p
INNER JOIN Kontakte k
ON k.RefPerson=k.ID
OPEN cur_refperson

FETCH NEXT FROM cur_refperson
INTO @id,@email,@email2,@email3,@reffirma

WHILE @@FETCH_STATUS = 0  
    BEGIN
    
    UPDATE Kontakte
    SET RefPerson=@id,RefFirma=@reffirma
    WHERE (CONVERT(nvarchar,Info)=@email)
    OR (CONVERT(nvarchar,Info)=@email2)
    OR (CONVERT(nvarchar,Info)=@email3)

    Fetch Next from cur_refperson INTO  @id,@email,@email2, @email3,@reffirma

    END
CLOSE cur_refperson;
DEALLOCATE cur_refperson;

CodePudding user response：

Your primary issue is almost certainly due to the fact that you did not specify a length for nvarchar in the CONVERT. You should have had CONVERT(nvarchar(250), Info) or similar

However, there should be no need to specify this conversion anyway, as SQL Server will automatically widen to nvarchar in any case.

Furthermore, the whole cursor is unnecessary. You should just do a normal joined update. The join condition is not entirely clear, but it seems it is k.Info IN (p.email, p.email2, p.email3)

UPDATE k
SET RefPerson = p.ID,
    RefFirma= p.RefFirma
FROM Kontakte k
JOIN Personen p ON k.Info IN (p.email, p.email2, p.email3);