SQl：使用用户定义的函数更新表 [英] SQl: Update the table using user defined functions

问题描述

大家好，



如何使用用户定义的函数更新每个coulmn值，我尝试使用光标，但需要很长时间才能执行，我相信这可以通过简单的查询完成，但我无法提出解决方案。



//表格数据



Record_Number TransactionTime Acktime

1 16/07/2014 Null

2 14/07/2014 Null

3 05/07 / 2014 Null





udf_getacktime的功能：它需要Record_number并对不同的表执行某些条件操作并获得相同的时间和恢复时间。



我的要求：获取每条记录的acktime否并更新记录的时间



代码如下：



tbltemp（Record_Number，TransactionTime，acktime = null） - 最初的确认时间null



udf_getAckTime （record_Number） - 函数接受RecordNo并返回acktime



我的更新应如下所示：

更新tbltemp

set acktime = udf_getAckTime（record_Number）

其中record_number应该是我作为参数发送的记录号。





如果你有任何办法可以帮助我。



问候

Prashant P

Hi All,

How do I update the each coulmn values using using user defined functions , I tried with cursor but its taking long time to execute and I believe that this can be done using simple query but I am unable to come up with solution.

//table data

Record_Number TransactionTime Acktime
1 16/07/2014 Null
2 14/07/2014 Null
3 05/07/2014 Null


Functionality of the udf_getacktime : It takes the Record_number and performs some condition operation on different tables and get the acktime and resturns the same.

My requirement: Get the acktime for each record no and update the time for the record

The code is below:

tbltemp(Record_Number,TransactionTime,acktime=null) -- Acktime initially null

udf_getAckTime(record_Number) --Function takes the RecordNo and returns acktime

My update should be as below:
Update tbltemp
set acktime =udf_getAckTime(record_Number)
Where record_number should be the record no which I am sending as a parameter.


Can any one help me if you have any approach.

Regards
Prashant P

推荐答案

你可以通过CROSS APPLY做到这一点，请看下面的示例：

You can do this through CROSS APPLY, please look at the sample below:

UPDATE A SET A.acktime=B.recordvalue FROM tbltemp A
CROSS APPLY 
( 
   SELECT udf_getAckTime(A.acktime) AS recordvalue
) B
WHERE record_number='your Record Number'

虽然此代码仅供您参考，您可以对其进行更优化。

Although this code is only for your reference and you can optimize it more.

对于该功能，您可以使用：虽然 [ ^ ]循环，while循环+ 光标 [ ^ ]最后一个是公用表格式 [ ^ ]。





我几乎可以肯定你可以使用简单的 UPDATE + SELECT 查询：

For that functionality you can use: While[^] loop, While loop + cursor[^] and the last one is Common Table Expressions[^].


I'm almost sure you can use simple UPDATE + SELECT query:

UPDATE t1 SET t1.DestinationField = t2.SourceField
FROM DestinationTable AS t1 INNER JOIN SourceTable AS t2 ON t1.Key = t2.ForeignKey

其中 Key - 在您的情况下 - 将 Record_Number 。

where Key - in your case - would be Record_Number.

UPDATE t1 SET t1.AckTime = udf_GetAckTime(t2.AckTime)
FROM YourTableName AS t1 INNER JOIN YourTableName AS t2 ON t1.Record_Number = t2.Record_Number

[/ EDIT]

[/EDIT]

这篇关于SQl：使用用户定义的函数更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！