{"id":7735,"date":"2020-02-06T02:39:54","date_gmt":"2020-02-06T09:39:54","guid":{"rendered":"https:\/\/database.guide\/?p=7735"},"modified":"2020-02-09T15:40:00","modified_gmt":"2020-02-09T22:40:00","slug":"update-data-via-a-table-valued-function-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/update-data-via-a-table-valued-function-in-sql-server\/","title":{"rendered":"Update Data via a Table-Valued Function in SQL Server"},"content":{"rendered":"

In SQL Server<\/a>, it\u2019s possible to update data via a table-valued function<\/a>. <\/p>\n

What I mean is, you can update data in the underlying tables that the function queries. <\/p>\n

For example, if your function returns someone’s first name from a table, you can update their first name by running an UPDATE<\/code> statement against the function instead of the table.<\/p>\n

Note that this only works on inline table-valued functions (ITVFs). As far as I’m aware, it won’t work on multi-statement table-valued functions (MSTVFs). <\/p>\n

Also, the columns you update will need to be valid columns in the function’s query.<\/p>\n

<\/p>\n

Example 1 – Sample Function<\/h2>\n

Here’s a quick function that selects basic data from a table.<\/p>\n

\r\nCREATE FUNCTION udf_GetScore_ITVF( @Player varchar(255))\r\n    RETURNS TABLE\r\nAS\r\nRETURN (\r\n    SELECT Score\r\n    FROM dbo.Scoreboard\r\n    WHERE Player = @Player\r\n    );\r\nGO\r\n<\/pre>\n
This function simply selects the score for a given player.<\/p>\n
I could use this function to update a player’s score.<\/p>\n
I realise that you’d typically return more than one column when using a table-valued function, but I want to keep this example simple for demonstration purposes.<\/p>\n
Example 2 – Update Data via the Function<\/h2>\n
Here’s an example of updating the player’s score.<\/p>\n
First, let’s see what that player’s current score is.<\/p>\n
\r\nSELECT * FROM udf_GetScore_ITVF('Homer');\r\n<\/pre>\n
Result:<\/p>\n
\r\n+---------+\r\n| Score   |\r\n|---------|\r\n| 1       |\r\n+---------+\r\n<\/pre>\n
So Homer has a score of 1.<\/p>\n
Let’s use the table-valued function to increase it.<\/p>\n
\r\nUPDATE udf_GetScore_ITVF('Homer') SET Score = 99999;\r\n\r\nSELECT * FROM udf_GetScore_ITVF('Homer');\r\n<\/pre>\n
Result:<\/p>\n
\r\n+---------+\r\n| Score   |\r\n|---------|\r\n| 99999   |\r\n+---------+\r\n<\/pre>\n
So I successfully increased Homer’s score using the inline table-valued function.<\/p>\n
Example 3 – When it Won’t Work<\/h2>\n
The actual columns you’re able to update will depend on the columns being selected in the query. My query only selects the Score<\/code> column, so I can only update the data in that column.<\/p>\n
Here’s what happens if I try to update data in another column.<\/p>\n
\r\nUPDATE udf_GetScore_ITVF('Homer') SET Player = 'Apu';\r\n<\/pre>\n
Result:<\/p>\n
\r\nMsg 207, Level 16, State 1, Line 1\r\nInvalid column name 'Player'.\r\n<\/pre>\n
We know that the table has a Player<\/code> column (because it’s in the WHERE<\/code> clause of the function). However, it’s not selected in the query, and therefore, it isn’t a valid column to update.<\/p>\n","protected":false},"excerpt":{"rendered":"
In SQL Server, it\u2019s possible to update data via a table-valued function. What I mean is, you can update data in the underlying tables that the function queries. For example, if your function returns someone’s first name from a table, you can update their first name by running an UPDATE statement against the function instead … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,61,98,99],"class_list":["post-7735","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-t-sql","tag-table-valued-functions","tag-udf"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7735","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=7735"}],"version-history":[{"count":13,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7735\/revisions"}],"predecessor-version":[{"id":7781,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7735\/revisions\/7781"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=7735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=7735"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=7735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}