473,854 Members | 1,757 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to update using a function

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. For this
example, let's say they are twice A and three times B, respectively:

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,B)

Right? Well, no. Instead, I get this message:

Server: Msg 155, Level 15, State 1, Line 2
'A' is not a recognized OPTIMIZER LOCK HINTS option.

Any suggestions? I would like to use this structure, if possible.

Jim Geissman
Countrywide Home Loans
Jul 20 '05 #1
7 35090

"Jim Geissman" <ji**********@c ountrywide.com> wrote in message
news:b8******** *************** ***@posting.goo gle.com...
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. For this
example, let's say they are twice A and three times B, respectively:

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,B)

Right? Well, no. Instead, I get this message:

Server: Msg 155, Level 15, State 1, Line 2
'A' is not a recognized OPTIMIZER LOCK HINTS option.

Any suggestions? I would like to use this structure, if possible.

Jim Geissman
Countrywide Home Loans


Unfortunately, this doesn't work since you can't use column names as
parameters in this situation. 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.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.F2(A), D=dbo.F3(B)

But scalar functions perform badly on large data sets, because they are
invoked per-row, so this might not be appropriate. If neither of these
options are useful, then the simplest solution may just be a stored
procedure which does the UPDATE.

Simon
Jul 20 '05 #2
You need a scalar UDF instead of a table-valued UDF:

CREATE FUNCTION dbo.FA(@A INTEGER)
RETURNS INTEGER
AS
BEGIN
RETURN @A*2
END
GO

CREATE FUNCTION dbo.FB(@B INTEGER)
RETURNS INTEGER
AS
BEGIN
RETURN @B*3
END
GO

UPDATE #T
SET c=dbo.FA(a),
d=dbo.FB(b)

Don't store calculated results in the table if you can help it. If C and D
are always calculated from A and B then just drop the C and D columns and do
the calculation in a view or query instead.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #3
> Thanks for the suggestions. Unfortunately, in the real case, the function
values are a property price index and its estimated reliability, which
are computed from relatively elaborate lookups and calculations based on
zip code, effective date and property type. The key point to me is that variable names can't be used as function
parameters in a case like this. Too bad.


I'm not sure I understand why that is a problem. UDF parameters are passed
by value not by reference and in this respect my function is no different
from the one you posted - I just used different names.

If you post your function code (also DDL and sample data INSERT statements)
maybe we can help you rewrite it exactly as required. Also consider Simon's
point about the performance overhead of a UDF. Possibly you can use an
UPDATE statement, query or view to achieve the same result without using a
function.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #4
> Don't store calculated results in the table if you can help it. If C and D
are always calculated from A and B then just drop the C and D columns and do
the calculation in a view or query instead.


Thanks for the suggestions. Unfortunately, in the real case, the function
values are a property price index and its estimated reliability, which
are computed from relatively elaborate lookups and calculations based on
zip code, effective date and property type.

The key point to me is that variable names can't be used as function
parameters in a case like this. Too bad.

Jim
Jul 20 '05 #5
"David Portas" <RE************ *************** *@acm.org> wrote in message news:<kP******* *************@g iganews.com>...
Thanks for the suggestions. Unfortunately, in the real case, the function
values are a property price index and its estimated reliability, which
are computed from relatively elaborate lookups and calculations based on
zip code, effective date and property type.

The key point to me is that variable names can't be used as function
parameters in a case like this. Too bad.


I'm not sure I understand why that is a problem. UDF parameters are passed
by value not by reference and in this respect my function is no different
from the one you posted - I just used different names.

If you post your function code (also DDL and sample data INSERT statements)
maybe we can help you rewrite it exactly as required. Also consider Simon's
point about the performance overhead of a UDF. Possibly you can use an
UPDATE statement, query or view to achieve the same result without using a
function.


Sure, I can do this with two separate function calls, each of which returns
a single scalar value. However, computing value A and value B both visit a
lot of the same lookups and computations, so it seems to me there is
duplication of effort, hence lower than optimum efficiency. Because I'm
looking at doing this approx 60 million times, I would like to use that
work to compute both values at once. 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. I was simply hoping to combine all that work
and retrieve both results from one call.

I don't care about the extra time required to come up with the values. I
don't do the work -- a computer does it, and computers don't complain about
unnecessary work. I will simply adjust the schedule. 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
Jul 20 '05 #6
Jim Geissman (ji**********@c ountrywide.com) writes:
Sure, I can do this with two separate function calls, each of which
returns a single scalar value. However, computing value A and value B
both visit a lot of the same lookups and computations, so it seems to me
there is duplication of effort, hence lower than optimum efficiency.
Because I'm looking at doing this approx 60 million times, I would like
to use that work to compute both values at once. 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. I was simply hoping
to combine all that work and retrieve both results from one call.

I don't care about the extra time required to come up with the values.
I don't do the work -- a computer does it, and computers don't complain
about unnecessary work. I will simply adjust the schedule. 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.


There is some new syntax in the upcoming version of SQL Server that
permits you to call a table-valued function and passing column values
to it. I have not studied it in detail, though.

Anyway, this is SQL Server, and you could do one of two things that is
better than having two UDFs:

1) Return the two values in one, as a string, and then split that
string in the UPDATE statement. Sure is not going to give you
nice code, but at least you are not doing the compuation twice.

2) Keep your table-valued function, and set up a cursor on the
table to update, and update one row at a time:
UPDATE tbl
SET col3 = f.x, col4, f.y
FROM tbl t
CROSS JOIN f(@col1, @col2) f
WHERE t.keycol = @keyval
This may sound horrendeously inneffecient, but if you call an
scalar UDF in a set-based statement, you get something cursor-like
behind the scenes anyway.

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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
Thanks a lot, Erland!

Jim
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
4653
by: Valiz | last post by:
Hi, I am updating system time with IRIG time source which sends irregular pulses as shown below 11000011111100111111111111111111....(1-IRIG present and 0-IRIG not present) I need to update system time when I find a steady pulse or a series of 1's say for a span of 10-15 seconds. Is there a way to implement this in VB6? Thanks in advance. Valiz.
0
2129
by: Ferindo Middleton Jr | last post by:
I am trying to write a Perl Function for one of the databases I'm building a web application for. This function is triggered to occur BEFORE INSERT OR UPDATE. This function is complex in that it involves fields in two different tables which need to be updated, where the updates one receives depend on and must actually also reflect the same data entered into one of the tables explicitly by the user. I basically want to make sure that...
1
6251
by: Mike | last post by:
In C, we can typedef pointer to functions, and therefore use function tables. But what's the advantage of using function table? Thanks, Mike
1
1718
by: may | last post by:
Hi, I'm trying to update a SQLServer table via a call to a stored procedure using the DataSet.Update(dataset) function. What I'm not sure about is how does this dataset get interpreted on the Database side. I can't pass a dataset/table as a SQLParameter so how does it handle the dataset? Thanks -KimD
4
3428
by: Jim Hammond | last post by:
It would be udeful to be able to get the current on-screen values from a FormView that is databound to an ObjectDataSource by using a callback instead of a postback. For example: public void RaiseCallbackEvent(string eventArgs) { // update the data object with the values currently on screen FormView1.UpdateItem(true); }
2
8794
by: Newbie | last post by:
Could someone help: I have been searching the web trying to figure out a way to use function key's in my vb.net 2003 program (No luck!). What I would like to do is enable the user to press (for example) F4 and have a search dialog window come up. Or press 'Up' & 'Down' arrow's to toggle between two radio buttons? Thanks
3
1707
by: drsantosh82 | last post by:
Hi, I am trying to implement a callback routine using function pointers. Basically, I am trying to avoid tying my callback invoking member to a particular class. Let me explain my problem with an example. The code snippet below should help in explaining my problem better. class A
1
12092
by: tekedge | last post by:
Hi, I have to do an update using a query which uses CTE . Could any body please tell me how I can do an update. I am unable to update if I replace the final select with an update one. Thanks in advance Jay.
2
6876
by: nikhizumi | last post by:
advantages of using FUNCTION in C++
0
9903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9754
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10692
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9526
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7927
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5952
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4567
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4168
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.