Updating a table using a scalar subselect

Posted by / 17-Jul-2019 20:46

For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works.You can then answer the original question with the parent clause.I don't do the work -- a computer does it, and computers don't complain about unnecessary work. But it still seems odd that I can use the function approach to get values one at a time, but not two at a time.Jim Sure, I can do this with two separate function calls, each of which returns a single scalar value.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.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.For this example, let's say they are twice A and three times B, respectively: CREATE FUNCTION dbo. Instead, I get this message: Server: Msg 155, Level 15, State 1, Line 2 'A' is not a recognized OPTIMIZER LOCK HINTS option. For this example, let's say they are twice A and three times B, respectively: CREATE FUNCTION dbo. Instead, I get this message: Server: Msg 155, Level 15, State 1, Line 2 'A' is not a recognized OPTIMIZER LOCK HINTS option. In this specific case, you could simply create C and D as computed columns: CREATE TABLE #T (A int, B int, C as A*2, D as B*3) Alternatively, you could use scalar UDFs: create function dbo.

Sure, I can do this with two separate function calls, each of which returns a single scalar value.I would also like to use the elegance of a function, which makes the code easier to read.Two function calls preserve the elegance by hiding the details, and that's what I did last time, and will do this time.Possibly you can use an UPDATE statement, query or view to achieve the same result without using a function.-- David Portas SQL Server MVP -- Thanks for the suggestions.

updating a table using a scalar subselect-3updating a table using a scalar subselect-8updating a table using a scalar subselect-68

I was simply hoping to combine all that work and retrieve both results from one call.