Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Update SQL Table That has no Primary Key

PeterPetersen
8 - Asteroid

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:

CustomerNumberDateIsRecent
222205-31-2018100.000

 

To look for matches on CustomerNumber and Date (I.e. CustomerNumber=2222 and Date=05-31-2018)

 

CustomerNumberDateRevenue
111105-31-2018

50.000

222205-31-2018

9.000

333305-31-2018

75.000

 

And then update those matches with the new values so the table becomes:

 

CustomerNumberDateRevenue
111105-31-2018

50.000

222205-31-2018

100.000

333305-31-2018

75.000

4 REPLIES 4
JohnJPS
15 - Aurora

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
13 - Pulsar

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

 

image.png

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.

PeterPetersen
8 - Asteroid

Great stuff thanks all!

tmlmark
7 - Meteor

One way I solve for this is using the In-DB write out. 

ALTERYX_1.png

 

 

Your input (1.) can be streamed in (2.), creating a temporary table. 

 

temp_table.png

 

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.

 

field_mapping.png

 

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.

Labels