Updating a table using a scalar subselect Adult dating swing


05-May-2020 17:23

If neither of these options are useful, then the simplest solution may just be a stored procedure which does the UPDATE.

Simon You need a scalar UDF instead of a table-valued UDF: CREATE FUNCTION dbo.

F(@A int,@B int) RETURNS @Tbl TABLE (X int, Y int) AS BEGIN INSERT @Tbl (X, Y) VALUES (@A*2, @B*3) RETURN END Now we use the function to compute the other columns: UPDATE #T SET C=X, D=Y FROM dbo. F(@A int,@B int) RETURNS @Tbl TABLE (X int, Y int) AS BEGIN INSERT @Tbl (X, Y) VALUES (@A*2, @B*3) RETURN END Now we use the function to compute the other columns: UPDATE #T SET C=X, D=Y FROM dbo. F2(@i int) returns int as begin return @i*2 end create function dbo.

F3(@i int) returns int as begin return @i*3 end update #T set C=dbo. F3(B) But scalar functions perform badly on large data sets, because they are invoked per-row, so this might not be appropriate.

Take a table, where not all the columns are populated: CREATE TABLE #T (A int, B int, C int, D int) INSERT #T (A, B) VALUES (1,2) INSERT #T (A, B) VALUES (3,4) INSERT #T (A, B) VALUES (5,6) INSERT #T (A, B) VALUES (7,8) INSERT #T (A, B) VALUES (9,10) The values for C and D can be computed as functions of A and B. Jim Geissman Countrywide Home Loans Take a table, where not all the columns are populated: CREATE TABLE #T (A int, B int, C int, D int) INSERT #T (A, B) VALUES (1,2) INSERT #T (A, B) VALUES (3,4) INSERT #T (A, B) VALUES (5,6) INSERT #T (A, B) VALUES (7,8) INSERT #T (A, B) VALUES (9,10) The values for C and D can be computed as functions of A and B. Jim Geissman Countrywide Home Loans Unfortunately, this doesn't work since you can't use column names as parameters in this situation.

Then, of course, you could devise the computation in your function so that it works on all values in the table at once, but that may prove to be a real challenge.I was simply hoping to combine all that work and retrieve both results from one call.