I want to make a single SQL update call that updat...
# sql
m
I want to make a single SQL update call that updates multiple records at the same time. This can normally be done with an update with a select or an inner join:
Copy code
UPDATE Persons
SET  Persons.PersonCityName=(SELECT AddressList.PostCode
                            FROM AddressList
                            WHERE AddressList.PersonId = Persons.PersonId)
or
Copy code
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.
m
you cold insert your data coming in into a sql table variable and then join on that with your update as listed above... you would still need to loop over your data from ColdFusion and could end up with a huge query... but it is one way. and could all be done in one transaction
j
Or insert all records into a temp table then join and update. https://www.sqlservertutorial.net/sql-server-basics/sql-server-insert-multiple-rows/
d
Depending on your DB, you could potentially send the CF data as JSON and query against it as your table. Same goes for XML.
🤔 1
SQL Server has allowed querying against XML for a long time, where as JSON support was added in 2016 (I believe that was the version).
Most modern DB engines have support for one or both options.
m
@dswitzer We use JSON in a bunch of areas.
m
JSON in sql server I didn't realize that was an option... wow talk about new thoughts on data import / sync tasks...
m
@Michael Schmidt It has to be SQL Server 2016 (13.x) compatibility mode 130 or higher
@Michael Schmidt yeah we used to parse the JSON and add them into new columns. Now we just store the raw JSON and use the JSON functions to do the queries
m
I would still probably let SQL parse it but store it still in columns to let a RDBMS do what it's good at set data...
m
Doing an insert as mentioned above has a limitation of 1000 records. Another way you can also accomplish below without creating a table using values constructor:
Copy code
UPDATE 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