>I have a big update command containing a WHERE clause which will take the value from a select but with a condition:
>
>

>...
>     WHERE BusinessTime.NoProvince=
>      CASE
>       WHEN (SELECT Company.NoCountry FROM Company WHERE Company.Numero=Client.NoCompany)=1 THEN
>       (SELECT Company.NoProvince FROM Company WHERE Company.Numero=Client.Company) ELSE 84
>       END AND
>      BusinessTime.NoHoliday=0 AND
>      BusinessTime.Start<Client.FirstDate AND
>      BusinessTime.[End]>=Client.SecondDate) Temp)
>...
>

>
>Is there a way to avoid doing two select in the company table to satisfy that need?

;with cte as (select BT.*, Company.NoCountry, Company.NoProvince from BusinessTime BT INNER JOIN Company ON BT.....

UPDATE cte SET ...

So, turn the UPDATE into select into CTE first and then UPDATE CTE if there is always 1 to 1 relation between table to update and the other tables.