update temp table with results from sub query (actually it is a function)

[joost versteegen]

hi, i am having trouble with a query. how can i update a temp table with results from sub query (actually it is a function)? this works:

UPDATE @TEMP
SET
formula\_name = (SELECT name FROM dbo.fn\_GetFormulaForJob(wo\_id, oper\_id, seq\_no)),
formula\_version = (SELECT version FROM dbo.fn\_GetFormulaForJob(wo\_id, oper\_id, seq\_no))
FROM @TEMP

but i want something like this:

UPDATE  @TEMP 
SET formula\_name = r.name, 
formula\_version = r.version
FROM (SELECT name, version FROM dbo.fn\_GetFormulaForJob(wo\_id, oper\_id, seq\_no) r

thanks.

[ZurdoDev]

Treat it the same as a table

UPDATE @temp
SET formula_name = r.name, formula_version = r.version
FROM dbo.fn_GetFormulaForJob(wo_id, oper_id,seq_no) r

Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.

[joost versteegen]

thanks. i tried it, but i get the error 'Cannot call methods on table'. too bad. my original looks so inefficient, but it will have to do i guess.

[Richard Deeming]

How about:

UPDATE T
SET formula_name = r.name, forumula_version = r.verion
FROM @TEMP As T
CROSS APPLY dbo.fn_GetFormulaForJob(T.wo_id, T.oper_id, T.seq_no) As r;

Using APPLY | FROM (Transact-SQL) - SQL Server | Microsoft Docs[^] SQL Server CROSS APPLY and OUTER APPLY[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[joost versteegen]

wow, ok, i will try it first thing in the morning. thanks.

[joost versteegen]

this works! Thanks.