In SQL Server, it’s 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 of the table.

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).

Also, the columns you update will need to be valid columns in the function’s query.

Example 1 – Sample Function

Here’s a quick function that selects basic data from a table.

CREATE FUNCTION udf_GetScore_ITVF( @Player varchar(255))
    RETURNS TABLE
AS
RETURN (
    SELECT Score
    FROM dbo.Scoreboard
    WHERE Player = @Player
    );
GO

This function simply selects the score for a given player.

I could use this function to update a player’s score.

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.

Example 2 – Update Data via the Function

Here’s an example of updating the player’s score.

First, let’s see what that player’s current score is.

SELECT * FROM udf_GetScore_ITVF('Homer');

Result:

+---------+
| Score   |
|---------|
| 1       |
+---------+

So Homer has a score of 1.

Let’s use the table-valued function to increase it.

UPDATE udf_GetScore_ITVF('Homer') SET Score = 99999;

SELECT * FROM udf_GetScore_ITVF('Homer');

Result:

+---------+
| Score   |
|---------|
| 99999   |
+---------+

So I successfully increased Homer’s score using the inline table-valued function.

Example 3 – When it Won’t Work

The actual columns you’re able to update will depend on the columns being selected in the query. My query only selects the Score column, so I can only update the data in that column.

Here’s what happens if I try to update data in another column.

UPDATE udf_GetScore_ITVF('Homer') SET Player = 'Apu';

Result:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Player'.

We know that the table has a Player column (because it’s in the WHERE clause of the function). However, it’s not selected in the query, and therefore, it isn’t a valid column to update.