community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Unable to Update SQL server

Meteoroid

I created a new workflow with several joins and at the end, it creates a new table with all the columns that are needed. (Note: Cache selected) .Now, I updated one of the joining condition where its column initially as nulls during the previous run, so changed newly created table of the workflow with "Update option"  and it's throwing an error. pls suggest Capture8.PNGto fix

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @nkothakota 

 

Are you writing any sql statements in your Output Tool?

 

I guess this error occurs if you don't specify how you're going to update your table. 

Try to add an update statement and see if the error still remains.

 

Cheers,

Meteoroid

I didn't write any insert statement while creating the table using the workflow 

 

Do I need to write insert and update statement in the pre or post create sql statement?

 

my case: either a new record or update existing record based item_nbr. Could you please share me sample example. 

 

Alteryx Certified Partner
Alteryx Certified Partner

@nkothakota 

 

You're probably using the "Update, Insert if New" option, right?


Does the table have a primary key or something? 

 

I'm almost sure that you need to tell the Database what condition you're updating your records.

 

Cheers,

Meteoroid

Initially, I used to create New option during the first run of workflow .... Pls note: No sqls used 

 

This table is created as a result of workflow however, there are two columns item_nbr, old_nbr, UPC should always be unique 

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@nkothakota 

 

Ok, maybe you need to create the primary key so it knows how to update the table.

You may add a pre-sql statement to do that.

ALTER TABLE Your Table
ADD PRIMARY KEY (Column used to update)

 

Or it could be used as a Post-SQL statement after you create this new table of yours.

 

Cheers,

Meteoroid

While doing the changes as per your suggestion, I found that data loaded with duplicate entries though i have given left + Inner join for item_nbr

Alteryx Certified Partner
Alteryx Certified Partner

@nkothakota 

 

That's weird.

 

I guess this could be a problem with your dataset itself. Or it could be something wrong with the way you want to update your table (maybe PK is not actually a PK).

 

What about an update statement, have you tried that?

 

Cheers,

Meteoroid

The target table that i was created in PDW and source is teradata.

 

This is my entire workflow however this brings duplicates though i am doing left + Inner join so technically it should not bring duplicate records Capture9.PNG

Alteryx Certified Partner
Alteryx Certified Partner

@nkothakota 

 

I advise you to debug your workflow through each step to see where it actually gets duplicated.

You can use Data Investigation Tools, Browse Tool or even the Unique Tool itself to check that.


Cheers,

Labels