SQL syntax error using Output tile

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

Alteryx error - syntax.jpg Alteryx error - syntax 2.jpg

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

Output

Accepted answers

dakeefe

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!

All comments

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703