Error opening "SELECT * FROM ": No Columns Returned

Question

I am trying to push through an update command to a SQL SERVER table using the dynamic input tool. The query is set to:

SELECT 1; 
UPDATE [Database].[Schema].[Table] SET [Column] = 'Alpha'

Alpha is updated from a flow-variable. However, this does not work (unexpectedly) and "Error opening "SELECT * FROM ": No Columns Returned" is returned. Help is welcome.

P.S. : Variations like

SELECT TOP 1 * FROM [A_TABLE_WITH_ATLEAST_ONE_ROW]; 
UPDATE [Database].[Schema].[Table] SET [Column] = 'Alpha'

work.

cvalteryx · Answer

Hey @Biswarun ,

I'm assuming you are creating a column in 'Text Input' Tool [Field1] with the value you need to update.

And have your Input connection and SQL as below:

SELECT TOP 1 * FROM [Schema].[dbo].[TableName];
UPDATE [Schema].[dbo].[TableName] SET [Column] = 'Alpha';

Dynamic Input Tool -> Replace a Specific string as below:

Is this what you are looking for?

It's working for me. Do give it a try.

Note: The SELECT statement you are executing before UPDATE, will always give you previous results, if you are updating same table.