Hi all,
I have about 1.500 rows in an alteryx flow. I need to look through and SQL table of 15m rows and find these 1.500 rows, and then update them in the SQL table. The problem is there is no primary key. The only way to identify unique rows is by a combination of customer number and date. So a snippet of what I need to do:
Use this table:
CustomerNumber | Date | IsRecent |
2222 | 05-31-2018 | 100.000 |
To look for matches on CustomerNumber and Date (I.e. CustomerNumber=2222 and Date=05-31-2018)
CustomerNumber | Date | Revenue |
1111 | 05-31-2018 | 50.000 |
2222 | 05-31-2018 | 9.000 |
3333 | 05-31-2018 | 75.000 |
And then update those matches with the new values so the table becomes:
CustomerNumber | Date | Revenue |
1111 | 05-31-2018 | 50.000 |
2222 | 05-31-2018 | 100.000 |
3333 | 05-31-2018 | 75.000 |
Solved! Go to Solution.
Hi @PeterPetersen,
You could just join on both those columns, or if that is cumbersome (pulling all 15M rows into Alteryx for the purpose of joining), you could also try to Data Stream In your data into the database for an In-DB join.
Hope that helps!
John
Is this a one time update? If so, I'd suggest something like this:
Union the two together (your original SQL table and the 1500 updates you need to make), making sure to set a specific output order (check box at the bottom of the Union tool) and putting your updates on top. Then grab a Unique on CustomerNumber and Date. The "U" output at this point is what you want your table to look like, so in the Output tool, rather than doing an update do a Delete and Append. You're replacing all the records but the end result will "appear" to just update the 1500 records.
Because of your total record count, I'm not sure that I'd use this as a repeatable process (will likely take a bit of time), but if you just need to do it once than this is a quick and dirty way to get it done without a primary key.
Hope it helps! Feel free to reach out with questions.
Great stuff thanks all!
One way I solve for this is using the In-DB write out.
Your input (1.) can be streamed in (2.), creating a temporary table.
But the trick is in step 3. Set your field mapping to match between Source and Destination on only the fields that constitute a unique record identifier.
Once that 3rd step is set (using update rows), it will only update rows that are found in the source file and will leave all other rows alone.