Mark Berning
12/14/2022, 3:48 PMUPDATE Persons
SET Persons.PersonCityName=(SELECT AddressList.PostCode
FROM AddressList
WHERE AddressList.PersonId = Persons.PersonId)
or
UPDATE Per
SET
Per.PersonCityName=Addr.City,
Per.PersonPostCode=Addr.PostCode
FROM Persons Per
INNER JOIN
AddressList Addr
ON Per.PersonId = Addr.PersonId
However (using the example above) the Addresslist table is actually a CF dynamically generated query that does not exist in the SQL server DB.
I could accomplish this by looping over the Addresslist CF query and updating one row at a time, but that would make a bunch of SQL server calls instead of a single SQL server call, albeit a much larger call.Michael Schmidt
12/14/2022, 4:06 PMJohn Wilson
12/14/2022, 4:14 PMdswitzer
12/14/2022, 6:09 PMdswitzer
12/14/2022, 6:09 PMdswitzer
12/14/2022, 6:10 PMMark Berning
12/14/2022, 6:12 PMMichael Schmidt
12/14/2022, 6:41 PMMark Berning
12/14/2022, 6:53 PMMark Berning
12/14/2022, 6:54 PMMark Berning
12/14/2022, 6:56 PMMichael Schmidt
12/14/2022, 6:57 PMMark Berning
12/16/2022, 8:31 PMUPDATE v
SET v.BuildTemplateID = BT.BuildTemplate
FROM vlabResources_TMP v
CROSS APPLY (
VALUES
<cfloop query="_getTemplates.data" >
('#BuildTemplate#')<cfif currentrow neq RecordCount>,</cfif>
</cfloop>
) BT (BuildTemplate)
WHERE v.BuildTemplateID is NULL
AND v.vlabID = BT.LaunchTemplate