I have a table in an MS SQL DB with nearly 15 million rows and need to update a small subset but the ID that I need to extract is a substring.
The keys values are all ‘RX1-######’ and I need to extract a set where the number section after the ‘RX1-’ is > 977289 but I don’t want to have to fetch all 14 million rows to update about 240.
Is there a query that I can put into the DQ Query reader node that will be able to get just these rows out of the database?
I’m sure you can do it in a where clause in sql with functions but I suspect it will somewhat slow as certainly no index will be used.
Functions: substring with start at character 5 (if 0-based index hence use 4) and then cast to int and compare to your magic number
WHERE cast(SUBSTRING(key_field, 5, 6) AS int) >977289
(instead of 6 you have to replace with actual length of the numeric part).
Note this is untested pseudo code. Maybe it works, maybe you need to adjust it a bit.