使用函数的T-SQL更新表列

问题描述

我有下表:

RecordID
Name
Col1
Col2
....
ColN

RecordID是BIGINT PRIMARY KEY CLUSTERED IDENTITY(1,1)，并且RecordID和Name已初始化.其他列为NULL.

The RecordID is BIGINT PRIMARY KEY CLUSTERED IDENTITY(1,1) and RecordID and Name are initialized. The other columns are NULLs.

我有一个函数，可以通过Name返回有关其他列的信息.

I have a function which returns information about the other columns by Name.

要初始化我的表，我使用以下算法:

To initialized my table I use the following algorithm:

1. 创建一个环
2. 获取一行，选择其Name值
3. 使用所选名称执行功能，并存储其结果在临时变量中
4. 在表中插入临时变量
5. 移至下一条记录

1. Create a LOOP
2. Get a row, select its Name value
3. Execute the function using the selected name, and store its resultin temp variables
4. Insert the temp variables in the table
5. Move to the next record

有没有办法做到这一点而无需循环?

Is there a way to do this without looping?

推荐答案

交叉应用基本上是为此目的构建的

Cross apply was basically built for this

SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

使用应用"

UPDATE some_table
SET some_row = another_row,
    some_row2 = another_row/2
FROM some_table st
  CROSS APPLY
    (SELECT TOP 1 another_row FROM another_table at WHERE at.shared_id=st.shared_id)
WHERE ...

在更新语句中使用交叉应用

这篇关于使用函数的T-SQL更新表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！