This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
So as the title states I have a table in a MySQL database that has two columns designated a primary keys. When I run the job in Alteryx for the first time everything loads fine into the database. However when I try and run it a second time to update the data in the database I get the below error.
" Output Data (35) DataWrap2ODBC::SendBatch: [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.32-enterprise-commercial-advanced-log]Duplicate entry 'DPL2LPI' for key 'PRIMARY' Insert into `SC_WU_Exshare`(`User_ID`,`Max_Date_Time`,`Sum_Sec`,`Update_Type`) Values (?,?,?,?)"
Which appears to state that there are duplicate primary keys and I don't know why the values are "?,?,?,?".
I also noticed when I look at the Append field map section in the output data component. Only one column is labeled as (key) and I don't see anyway to edit that even selecting "custom mapping"
Thanks for the quick reply. My comments are below.
I am running the same data set however the action I have specified on the table is "Update; Insert if New" so based on this I am expecting the output to update the existing dataset regardless of the values. I am expecting it to use the two PK's (which together create a unique value) to source the correct row and update the relevant values with the incoming data. Is this the correct steps to accomplish this desired outcome?
I am already performing a summarize function within my job to create one unique row per my criteria (User and Date Time) and as such of the nature of my job the rows should be unique. Outputing the data into an Excel file and manually checking for the row in question confirms the data is unique. However in the interest of throughness I did go ahead and follow you suggested steps and all row counts were < 1. ( See below)
Based on the above I still believe that Alteryx is not recognizing that there are two PK's.
i see what you are trying to do and i will explain when Update , If new row option works
1. This option will work when there is a row which already loaded but changed in source ,so when you run it next time this will work. well, there is a catch, if ONLY one row has changed and But the other rows this will raise an error
so the workaround is,
Create a FULL JOIN with Source and Target then Append the LEFT Output
Check for the changed rows and use the another output tool with Update if new Option
Below Workflow Image explains it :)
Update: to test it modify Employee name in the text inout tool then run it
and also , i posted another solution similar to this .but ONLY with LEFT JOIN
Hmmm so you're saying that Alteryx doesn't allow updates and inserts to be done at the same time correct? That seems a bit odd based on other data transformation software i've used I would have thought this would be a base feature.
Either way if thats the case i'll have to use the work around.
I'm having the same issue here : I have a Mysql table with 2 primary key, and when I try to insert (update if new) into this table, ALteryx consider only one primary key and therefore does not insert all the lines I want.
The proposed solution is for me not a practical solution as you need to load the full table every time you want to insert/update a new line. So if you have one line to insert into a 1 million lines table, it will be very slow.
I have the solution to create a staging table (without PK ) and do a post Create SQL treatment :
INSERT INTO table ( SELECT * FROM staging_table ) ON DUPLICATE KEYS UPDATE SET field_to_change = VALUES( field_to_change )
I don't want to use this solution because it's just a workaround for me, and I don't want to create staging table everytime I want to do an insert.
So is there a way to support the multiple Primary keys on a MySQL database with the output tool? And if not I would really push to have this as a build in functionnality in Alteryx.