使用当前列作为函数输入参数的 T-SQL 更新表

发布于 2025-01-04 18:25:27 字数 674 浏览 1 评论 0原文

我正在尝试使用函数更新表列。该函数的输入参数是我要更新的表中的数据字段。

假设我有一个包含两列（“国家/地区”和“首都”）的表格。输入“首都”，我正在使用一个函数，该函数按首都名称作为输入参数返回县名。所以，我的更新代码是这样的：

UPDATE @TableName
SET Country=(SELECT Country FROM dbo.fn_GetCountryByCapital(Capital))

IntelliSence 没有生成错误，但按 F5 时会显示：

“Capital”附近的语法不正确。

请注意，这只是一个示例（因为它可能在您看来很愚蠢）。我给出了示例来描述我的问题。我的真实情况包括更新语句中几个函数的使用。

预先感谢您的帮助。 Joro

可能的解决方案：

我找到了其他方法来做到这一点。它看起来不太好，但它有效：

我在临时表中添加了索引，以便使用 while 语句
对于表中的每条记录（使用 while 语句），我使用临时变量来存储我需要的字段信息
然后我已将此信息传递给我的函数以及我用来更新表的结果

我的猜测是，包围 select 语句和函数的括号“( )”不允许函数使用表中的正确值。

原文

I am trying to update table columns using a function. The input parameters of the function are data fields from the table that I want to update.

Let's say I have table with two columns ("Country" and "Capital"). The "Capital" is entered and I am using a function that returns a county name by capital name as input parameter. So, my update code is something like this:

UPDATE @TableName
SET Country=(SELECT Country FROM dbo.fn_GetCountryByCapital(Capital))

There is no error generated by IntelliSence,but on F5 press it say:

Incorrect syntax near 'Capital'.

Please, note that this is just a example (because it may looks to you silly). I give it sample in order to describe my problem. My real situation includes the use of several functions in the update statement.

Thank you in advance for the help.
Joro

Possible Solution:

I have found other way to do this. It does not look so good, but it works:

I have added index in my temp table in order to use while statement
For each record in the table (using while statement) I have used temp variables to store the field information I have need
Then I have passed this information to my functions and the outcome I have used to update the table

My guess is that the brackets '( )' that surrounded the select statement and the function do not allowed the function to use the correct values from the table.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏日浅笑〃 2025-01-11 18:25:27

学习构建 SQL 的正确方法（最有效）：

UPDATE a
    SET Country=b.Country
    FROM @TableName a
    INNER JOIN YourCountryCapitalTable b ON a.Capital=b.Capital

您不能像应用程序一样编写 SQL，您需要使用集合逻辑而不是每行逻辑。当您将一堆函数放入 SQL 语句中时，它们很可能需要每行运行，从而减慢查询速度（除非它们是 FROM 子句中的表函数）。如果只是将该函数合并到查询中，您很可能会看到巨大的性能改进，因为索引使用和操作发生在整个查询中，而不是逐行进行。

令人遗憾的是，必须使用非常不优雅并且经常在各处重复的 SQL 代码。然而，您的主要 SQL 目标是快速数据检索（索引使用和集合操作），而不是一些花哨的编码选美比赛。

我找到了其他方法来做到这一点。 yuck yuck yuck，听起来像是一个未来的问题，当下一个人需要维护这段代码时。您不需要索引即可使用 WHILE。如果临时表中有太多行需要索引，那么 WHILE 是您应该做的LAST事情！

learn the right way (most efficient) to build SQL:

UPDATE a
    SET Country=b.Country
    FROM @TableName a
    INNER JOIN YourCountryCapitalTable b ON a.Capital=b.Capital

you can not code SQL like an application program, you need to use set logic and NOT per row logic. When you throw a bunch of functions into a SQL statement they most likely will need to be run per row, slowing down your queries (unless they are table functions in your FROM clause). If just incorporate the function into the query you can most likely see massive performance improvements because of index usage and operations occur on the complete and not row per row.

it is sad to have to very sql code that isn't elegant and often repeats itself all over the place. however, your main sql goal is fast data retrieval (index usage and set operations) and not some fancy coding beauty contest.

I have found other way to do this. yuck yuck yuck, sounds like a future question here on SO when the next person needs to maintain this code. You don't need an index to use a WHILE. If you have so many rows in your temp table that you need an index, a WHILE is the LAST thing you should be doing!

回复收藏 0 原文

~没有更多了~