Update set select where condition using functions T-SQL SQL Server

Question

I have to process some modification on values of a table like following

DECLARE @tmpMap TABLE(Ptc int, Path int);

INSERT INTO @tmpMap(Ptc, Path) 
    SELECT ContactPointId_fk, PathId_fk 
    FROM t_PathContactPoint

UPDATE @tmpMap 
SET Path = (SELECT dbo.HKA_GetLeafIDFromPath(Path))

As you can see I am creating temporary table which allows me to process calculations with a scalar function that I created which returns int.

What I want to do is to specify that I need to update only rows where the result of the function is different from -1.

UPDATE @tmpMap 
SET Path = (SELECT dbo.HKA_GetLeafIDFromPath(Path) 
WHERE (the result of the function is different from -1))

Any ideas on how to do this with T-SQL in SQL Server please?

Gordon Linoff · Accepted Answer · 2017-10-29 01:22:23Z

1

Use APPLY:

UPDATE t
SET Path = g.LeafId 
FROM @tmpMap t CROSS APPLY
     ( VALUES ( dbo.HKA_GetLeafIDFromPath(Path) ) ) g(LeafId)
WHERE g.LeafId <> -1;

edited Oct 29, 2017 at 1:22

answered Oct 29, 2017 at 1:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Haithem KAROUI Over a year ago

i got the following error : Table-valued function 'HKA_GetLeafIDFromPath' cannot have a column alias.

Haithem KAROUI Over a year ago

Just Modify your answer and put CROSS APPLY (select dbo.HKA_GetLeafIDFromPath(Path)) as g(LeafId) i.e: add select before the function call and ill check it as valid answer. thank you for pointing CROSS APPLY which resolved my issue.

Gordon Linoff Over a year ago

@KarouiHaythem . . . Duh. It's not a table valued function, it is just a regular function. I would more typically do this with values().

Collectives™ on Stack Overflow

Update set select where condition using functions T-SQL SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related