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: CustomerNumberDateIsRecent222205-31-2018100.000 To look for matches on CustomerNumber and Date (I.e. CustomerNumber=2222 and Date=05-31-2018) CustomerNumberDateRevenue111105-31-201850.000 222205-31-20189.000 333305-31-201875.000 And then update those matches with the new values so the table becomes: CustomerNumberDateRevenue111105-31-201850.000 222205-31-2018100.000 333305-31-201875.000

Update SQL Table That has no Primary Key

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

Output

Accepted answers

danrh

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.

solution.yxmd

All comments

JohnJPS

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

danrh

Is this a one time update? If so, I'd suggest something like this:

Hope it helps! Feel free to reach out with questions.

solution.yxmd

PeterPetersen

Great stuff thanks all!

tmlmark

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703