Merging SQL Server with a Case?

Question

Hi guys, here with another question regarding sql server 2008 r2 this time about merging, is it possible to have a case inside the merge update part?

Because he is telling me

Msg 156, Level 15, State 1, Line 9 Incorrect syntax next to the keyword "CASE".

MERGE INTO PERSONAFISICA AS TARGET USING dbo.#temp1 AS SOURCE ON TARGET.RFC = SOURCE.RFC AND TARGET.APATERNO = SOURCE.APELLIDO_PATERNO AND TARGET.AMATERNO = SOURCE.Apellido_Materno WHEN MATCHED THEN UPDATE SET TARGET.NUM_CLIENTE = 0, TARGET.NOMBRE1 = LEFT(SOURCE.Nombre,CHARINDEX(' ', SOURCE.NOMBRE + ' ') -1), CASE WHEN LEN(SOURCE.NOMBRE) - LEN(REPLACE(SOURCE.NOMBRE,' ','')) >= 1 THEN TARGET.NOMBRE2 = SUBSTRING(SOURCE.NOMBRE, CHARINDEX(' ', SOURCE.NOMBRE)+1, LEN(SOURCE.NOMBRE)) ELSE '' END, TARGET.APATERNO = SOURCE.Apellido_Paterno, TARGET.AMTERNO = SOURCE.Apellido_Materno, CASE WHEN SOURCE.SEXO = 'F' THEN TARGET.IDGENERO = 2 WHEN SOURCE.SEXO = 'M' THEN TARGET.IDGENERO = 1 ELSE TARGET.IDGENERO = 0 END, CASE WHEN SOURCE.ESTADO_CIVIL = '0' THEN TARGET.idestado_civil = 0 WHEN SOURCE.ESTADO_CIVIL = 'C' THEN TARGET.idestado_civil = 1 WHEN SOURCE.ESTADO_CIVIL = 'D' THEN TARGET.idestado_civil = 2 WHEN SOURCE.ESTADO_CIVIL = 'S' THEN TARGET.idestado_civil = 3 WHEN SOURCE.ESTADO_CIVIL = 'V' THEN TARGET.idestado_civil = 5 WHEN SOURCE.ESTADO_CIVIL = 'U' THEN TARGET.idestado_civil = 6 ELSE TARGET.idestado_civil = 0 END, TARGET.idregimen = 0, TARGET.saludo = SOURCE.SALUDO, TARGET.conyuge_nombre1 = '', TARGET.conyuge_nombre2 = '', TARGET.conyuge_apaterno = '', TARGET.conyuge_nombre2 = '', TARGET.dependiente = 0, TARGET.edad_dependiente = '', TARGET.ididentificacion = 0, TARGET.fecha_nacimiento = '1800-01-01', TARGET.rfc = SOURCE.RFC, TARGET.CURP = '', TARGET.idnacionalidad = 0, TARGET.email = SOURCE.Email_Personal, TARGET.idescolaridad = SOURCE.Escolaridad WHEN NOT MATCHED THEN INSERT (num_cliente, nombre1, nombre2, apaterno, amaterno, idgenero, idestado_civil, idregimen, saludo, conyuge_nombre1, conyuge_nombre2, conyuge_apaterno, conyuge_amaterno, dependiente, edad_dependiente, ididentificacion, fecha_nacimiento, rfc, curp, idnacionalidad, email, idescolaridad) VALUES (0, LEFT(nombre,CHARINDEX(' ',nombre + ' ')-1) AS [Primer Nombre], CASE WHEN LEN(nombre) - LEN(REPLACE(nombre,' ','')) >= 1 THEN SUBSTRING(nombre, CHARINDEX(' ', nombre)+1, LEN(nombre)) ELSE '' END AS [Segundo Nombre], Apellido_Paterno,Apellido_Materno) CASE WHEN SEXO = 'F' THEN 2 WHEN SEXO = 'M' THEN 1 ELSE 0 END, CASE WHEN Estado_Civil = '0' THEN 0 WHEN Estado_Civil = 'C' THEN 1 WHEN Estado_Civil = 'D' THEN 2 WHEN Estado_Civil = 'S' THEN 3 WHEN Estado_Civil = 'V' THEN 5 WHEN Estado_Civil = 'U' THEN 6 ELSE 0 END, 0, Saludo,'','','','',0,'',0,'1800-01-01',RFC,'',0,Email_Personal,Escolaridad 

Answer 1

You cannot use a case this way in an update. It should be like ...

 UPDATE table SET value1 = CASE WHEN a.blah = b.blah THEN foo WHEN a.blah > b.blah THEN bar ELSE NULL END, value2 = 5, ......