[resolved] Delete rows from MSSql table based on values in tMap row output?

One Star

[resolved] Delete rows from MSSql table based on values in tMap row output?

I need to delete rows from an MSSql table where their primary key values match the values in a tMap's row output column of values.
I'm trying a tMSSql Row component, loaded with the schema of the table (Providers). It's input is a row output (NPIsToBeDeleted) of a tMap that has a single column (BadNPIs).
I'm trying this query:
"DELETE FROM Providers
WHERE NPIsToBeDeleted.BadNPIs = Providers.PrimaryNPI"
It compiles OK, but I get a runtime error:
The multi-part identifier "NPIsToBeDeleted.BadNPIs" could not be bound (repeated a lot of times).
What have I got wrong?
Is there a better method?

Accepted Solutions
One Star

Re: [resolved] Delete rows from MSSql table based on values in tMap row output?

Got it.
But had to figure out how to reference the global variable in the query string:
"DELETE FROM Providers
WHERE Providers.PrimaryNPI = " + globalVariable

All Replies
Five Stars

Re: [resolved] Delete rows from MSSql table based on values in tMap row output?

You got that error because your tmap producing more than one value which is not handle by sql row component.
so do one thing after tmap put --tFlowToIterate-- and after that get global variable form tflowtoiterate "PrimaryNPI" column value to delete the rows..
Hope this solve your problem..
One Star

Re: [resolved] Delete rows from MSSql table based on values in tMap row output?

Got it.
But had to figure out how to reference the global variable in the query string:
"DELETE FROM Providers
WHERE Providers.PrimaryNPI = " + globalVariable
Seven Stars

Re: [resolved] Delete rows from MSSql table based on values in tMap row output?

Why are you not just using tMSSqlOutput to delete the records?