How to update a table column based on output from a table-based function

Question

I have 2 tables:

Course Table

CourseId	CatalogNumber	MajorVersion	CreatedDate
1	ABC	1.2	1-2-21
2	ABC	1.3	2-2-21
3	123	.4	5-2-21

State Table

Course	MajorVersion

ABC

For every record in the State table (tens of thousands), I want to populate the latest version from the Course table.

I created a table-based function to get the latest version:

function fn_get_latest_major_course
(
    @course nvarchar(50) 
)
returns table
as
return
(
    select top(1) [c].[CourseID], c.[MajorVersion] from [dbo].[Course] c where 
        ([CatalogNumber] = @course)
        order by [c].[CreatedDate] desc
);

I'm trying to move the data with this query, passing in the Course column for each State record to the table-based function to pull-back the MajorVersion

update State set MajorVersion = (select MajorVersion from fn_get_latest_major_course(Course))

But when I run this query I get this error:

Column name or number of supplied values does not match table definition.

This works just fine.

declare @catalogNumber nvarchar(50) = 'ABC'
select * from fn_get_latest_major_course(@catalogNumber)

So how do I, for every row in a table, call a table-based function and use the output to update one or more columns?

squillman · Accepted Answer · 2021-10-01 20:14:07Z

2

You would use CROSS APPLY to run your function for each row in State. It's like doing a JOIN on the results of the function.

UDPATE s
SET s.MajorVersion = f.MajorVersion
FROM State s
CROSS APPLY fn_get_latest_major_course(course) f;

Note that this works in your case because your function is returning only a single row. If your function returned more than one row this would potentially blow up quickly.

answered Oct 1, 2021 at 20:14

squillman

13.7k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update a table column based on output from a table-based function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related