Pfthb

I need to update a column [Recipient on contract] in the table [Check_Result]. But the value to insert into the column is not a fixed string but a value from [All_Contracts] table. Thus, all rows to inset into [Recipient on contract] are unique and can be found with the keys [ID Contract] = [Référence]

The link between tables [Check_Result] and [All_Contracts] is [ID Contract] = [Référence]

Update [Check_Result] 
set [Recipient on contract] =
 If [Bénéficiaire] ="Personne morale" Then
 If [Organisme] is not null Then get [Organisme] 
 Else get [Professionnel de santé]
 Else 
 If [Professionnel de santé] is not null Then get [Professionnel de santé]
 Else get [Organisme]

Is It possible to use THEN in such situation? with a Update Case when then (select from inner join where) statement?

Thank you

Yes, each of the values in CASE statement can be replaced with nested select statement surrounded with '(' and ')', i.e. this is a valid statement:

update table1 set
 field1 = CASE WHEN field2 = 'some value'
 THEN (select field1 from table2 where table1.key_field = table2.key_field) 
 ELSE 'some default value'
 END

You can do the same with IIF statement too

update table1 set
 field1 = IIF(field2 = 'some value',
 (select field1 from table2 where table1.key_field = table2.key_field),
 'some default value')

Your update statement isn't quite clear. Which value you want to get from [All_Contracts] table? It can simplified like this:

Update [Check_Result] set
 [Recipient on contract] = IIF([Bénéficiaire] = "Personne morale",
 COALESCE([Organisme], [Professionnel de santé]), 
 COALESCE([Professionnel de santé], [Organisme]))

There you can replace [Professionnel de santé] or [Organisme] with (select SomeField from [All_Contracts] where [All_Contracts].[ID Contract] = [Check_Result].[Référence]).