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

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

Anonymous — Tue, 26 Feb 2013 21:16:42 GMT

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?

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

Anonymous — Wed, 27 Feb 2013 08:52:30 GMT

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..

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

Anonymous — Fri, 01 Mar 2013 21:57:26 GMT

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

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

alevy — Mon, 08 Apr 2013 00:09:19 GMT

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