Delete a row when a specific value is found
- FlowHeater-Team
- Topic Author
- Offline
- Admin
Less
More
13 years 11 months ago #2077
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Delete a row when a specific value is found was created by FlowHeater-Team
Translated submission in the German forum. You can read the original entry
here
.
Hello,
First of all I would like to express my admiration.
Your software is by far the best MySQL import tool I have ever encountered. TOP!
FlowHeater will spare me many hours of work and frustration in the future.
However, I do have one query.
When I perform an Update to MySQL database from an Excel table I would like to do the following:
Delete a row when a specific value is found in a given column.
Is this feasible?
Daniel
Hello,
First of all I would like to express my admiration.
Your software is by far the best MySQL import tool I have ever encountered. TOP!
FlowHeater will spare me many hours of work and frustration in the future.
However, I do have one query.
When I perform an Update to MySQL database from an Excel table I would like to do the following:
Delete a row when a specific value is found in a given column.
Is this feasible?
Daniel
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
- FlowHeater-Team
- Topic Author
- Offline
- Admin
13 years 11 months ago #2078
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Delete a row when a specific value is found
Hello Daniel,
Thanks very much for your compliments. We always enjoy receiving feedback like yours.
Your requirement is certainly doable; it just needs a little manual preparation. You need a .NET Script Heater , a Filter Heater and the following short C# script.
Drag a .NET Script Heater and a Filter Heater onto the Designer . Connect the Excel field deciding your deletion criteria to the first input of the .NET Script Heater. As its second input connect the Excel field that contains the Primary Key information for the Update. Next connect the output of the.NET Script Heater to the Filter Heater. Now you have to copy and paste the above script code into the .NET Script Heater properties and manually adapt it for your MySQL table name, your MySQL Primary Key field name and your filter/deletion criteria. Take note of the comments in the script.
CAUTION: Please take care when making use of this method. If an incorrect SQL WHERE condition is entered here you could inadvertently delete the wrong database rows or even the entire table contents!
Thanks very much for your compliments. We always enjoy receiving feedback like yours.
Your requirement is certainly doable; it just needs a little manual preparation. You need a .NET Script Heater , a Filter Heater and the following short C# script.
Code:
public object DoWork()
{
bool bRet = false;
if (InValues.Length != 2)
throw new Exception("2 input parameter expected");
// do not execute in test mode
if (AdapterWrite.OnlyTest)
return bRet;
// 1. input parameter = filter / delete criteria
string filter = (string)InValues[0].GetString();
// for example; if filter = 5 then delete this record
if ( filter == "5" )
{
// 2. input parameter = PrimaryKey
string id = (string)InValues[1].GetString();
// build DELETE SQL
// !!! CAUTION: you have do use your table and field names !!!
string sql = "delete from YOUR_TABELLE where id = " + id.Replace(".", "").Replace(",", "");
// if your field type is char or varchar use this syntax
//string sql = "delete from YOUR_TABELLE where id = '" + id + "'";
// Execute the SQL statement on the WRITE
AdapterWrite.Execute(sql);
// filter this record
bRet = true;
}
return bRet;
}
Drag a .NET Script Heater and a Filter Heater onto the Designer . Connect the Excel field deciding your deletion criteria to the first input of the .NET Script Heater. As its second input connect the Excel field that contains the Primary Key information for the Update. Next connect the output of the.NET Script Heater to the Filter Heater. Now you have to copy and paste the above script code into the .NET Script Heater properties and manually adapt it for your MySQL table name, your MySQL Primary Key field name and your filter/deletion criteria. Take note of the comments in the script.
CAUTION: Please take care when making use of this method. If an incorrect SQL WHERE condition is entered here you could inadvertently delete the wrong database rows or even the entire table contents!
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in or Create an account to join the conversation.
Time to create page: 0.258 seconds