CODEWITHSUNDEEP
sqlsql-servert-sql
Haithem KAROUI
Haithem KAROUI

Reputation: 1611

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

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?

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use APPLY:

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

Upvotes: 1

Related Questions