>I have a CASE WHEN condition I need to implement based on the select value.
>
>Lets say I have this:
>
>

>UPDATE Client
> SET NoProvince=
>  CASE
>   WHEN (SELECT COALESCE((SELECT TOP 1 Invoice.AddUser FROM Invoice
>    WHERE Invoice.NoClient=Client.Numero AND Invoice.NoItem=23
>    ORDER BY Invoice.AI DESC),0))<>1 THEN AddUser ELSE 0
>  END
>

>
>Basically, I need to select the most recent record in the invoice table based on NoItem=23 condition. Then, based on the value collected, in this case Invoice.AddUser, I need to have a sub condition to only replace Client.NoProvince if Invoice.AddUser value is not 1.
>
>How can I fine tune this update to support this?

;with cte as (select client.*,  inv.AddUser as newUser, Inv.NoItem, 
 row_number() over (partition by Inv.NoClient order by Inv.Al DESC) as Rn from Client
inner join Invoice inv on Client.Numero = Inv.NoClient)

update cte set NoProvince = newUser where Rn = 1 and NoItem = 23