SQL Kata for Update query having select clause

Question

I want to generate SQL Server compatible query as below.

UPDATE RFI 
SET
RFI.ContractName = q.Name,
RFI.ParentID = q.ParentID
FROM (
SELECT PunchListMaster.Name, PunchListMaster.ParentID
FROM PunchListMaster
WHERE PunchListMaster.PID=12 -- {Request:PID}
)q
WHERE RFI.RFIID=34 -- {CurrentInstanceID}

Can you tell me the equivalent SQL Kata code which should be generated for the same. Any other suggestions to achieve this kind of structure is also welcomed.

Answer 1

You have to join the target table in SQL Server when it has a FROM clause. Maybe something like this:

UPDATE RFI 
SET
RFI.ContractName = q.Name,
RFI.ParentID = q.ParentID
FROM 
RFI
INNER JOIN 
(
SELECT PunchListMaster.Name, PunchListMaster.ParentID
FROM PunchListMaster
WHERE PunchListMaster.PID=12 -- {Request:PID}
)q
ON RFI.RFIID=34 -- {CurrentInstanceID}

You will be joining a single record subquery with the target table. There must be an on clause for the join and restricting based on the target table key produces one record to update.

I'm just looking at SQLKata, so I'm not sure how that would be specified there, but the problem is in your target SQL query.