SQL syntax error using Output tile

[dakeefe]

Hiya folks...

I'm having trouble with an output tile. Right now I have a workflow which generates data that I want to output into a pre-existing table on a MySQL server using output option "Update; Insert if New". However, when I run the workflow, I get the following error:

Output Data (3) Error SQLPrepare: [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.14]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `BOM_ID`=?' at line 1

The output tile isn't custom configured passed changing the output option as noted. See graphics below

I'm currently using Alteryx Admin Designer version 2018.4.5.55178. Any assistance would be much appreciated.

[adamorse]

I'm guessing maybe BOM_ID is part of the primary key in the database you are writing to, but not included in the data you are writing from the Select tool. I think when the output tool does this it essentially writes a SQL update query, and so needs to specify which record to update using the key. Not being able to see more of what you are doing makes it hard to tell....do you have a BOM_ID in the data coming out of the select?

[dakeefe]

adamorse - 

BOM_ID is the primary key in the destination table. It is also the name of the field being queried in the Input tile, and comes out of the Select tile.

[adamorse]

Strange! I feel like I got this error or one very much like it when I was missing data for a primary key and doing an update/insert if new.

So just to get myself up to speed, you have BOM_ID, and you change the datatype to string since it is a string in the target database? Some of the BOM_ID's might match the target and some might not? What other fields are being loaded into the database?

[dakeefe · Accepted solution]

adamorse - your last comment inspired me to try something different, and I solved it!

The Output tile doesn't like it when the target data table contains fields that aren't in the output itself.

The target table I'm writing to has 2 fields: BOM_ID (the primary key), and FLIP_DATE (a regular date field). In the original workflow I posted a picture of, I was only outputting BOM_ID, intending for the Output tile to insert new BOM_IDs into the table that were not already there. FLIP_DATE was going to get updated in a different workflow, so I didn't think I needed to have that field present in this one. It turns out that was a mistake.

I used a Formula tile to add the FLIP_DATE field, and a default value of ""  to my output, and that worked!