Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Update query in SQL component

I'm trying to put an update query in a SQL component. Is that not supported? Even with very simple updates, the component won't validate, giving me an error: "Validation error. Could not retrieve metadata from this query." And if I try to run it from inside the component, I get a syntax error. But if I copy the SQL from the component's SQL tab, I can run it just fine directly in Redshift.

I thought perhaps updates just weren't supported in this component, but the documentation (and other questions in the forums) suggest that they are.

4 Community Answers

Best Answer  >Matillion Agent  

Kalyan Arangam —

Hi Dov,

I think you are referring to documentation on “SQL Script” orchestration component instead of “SQL” transformation component.

The “SQL” transformation component cannot be used for DML. Only SELECTS are allowed. You may use DML/DDL statements in the “SQL Script” orchestration component.

“SQL Script” orchestration – https://redshiftsupport.matillion.com/customer/en/portal/articles/2107012-sql-script
“SQL” transformation – https://redshiftsupport.matillion.com/customer/en/portal/articles/1991934-sql-component

I have raised a ticket to make it clearer on the respective page(s).

Best
Kalyan

Matillion Agent  

Craig Rouse —

Hi Dov,
The SQL Component is only used to SELECT data, no updating. Sorry.
Thanks,
Craig


Dov Rosenberg —

Thanks. The example used in the documentation is "DROP TABLE matillion_example_table". May be worth updating the docs to specify what is and isn't allowed.


Best Answer  Matillion Agent  

Kalyan Arangam —

Hi Dov,

I think you are referring to documentation on “SQL Script” orchestration component instead of “SQL” transformation component.

The “SQL” transformation component cannot be used for DML. Only SELECTS are allowed. You may use DML/DDL statements in the “SQL Script” orchestration component.

“SQL Script” orchestration – https://redshiftsupport.matillion.com/customer/en/portal/articles/2107012-sql-script
“SQL” transformation – https://redshiftsupport.matillion.com/customer/en/portal/articles/1991934-sql-component

I have raised a ticket to make it clearer on the respective page(s).

Best
Kalyan


Dov Rosenberg —

Ah, yes. That was exactly my problem. Thanks.

Post Your Community Answer

To add an answer please login