is it possible to remove row by not in via impex?

[ansellsilvans]

Hi All,

I want to write an impex which should remove all the row which is not matched with a column value.

Example: I want to remove all the rows which are not having a value as USD in the p_isocode column in currency table.

Any help is much appreciated.

Thanks and Regards, Ansell

[former_member620692]

You can try the following tested ImpEx:

 $dburl=jdbc:mysql://localhost/hybris67
 $dbusername=hybris67
 $dbpassword=hybris67
 $dbdriver=com.mysql.jdbc.Driver
 
 REMOVE Currency[batchmode=true];pk[unique=true];
 #% impex.initDatabase("$dburl", "$dbusername","$dbpassword","$dbdriver");
 "#%
 impex.includeSQLData( 
   ""SELECT PK FROM currencies WHERE p_isocode <>'USD'""
 );
 "

Notes:

1. Please replace the DB connection parameters as per your application.

2. I am assuming that USD is your Base currency. If USD is not your Base currency, the ImpEx mentioned above will remove all the currencies except USD and your Base currency; but you will also get an exception because of RemoveBaseCurrencyInterceptor.

3. I would go with Andrew's approach but I have answered it just because you have mentioned that you want to do it using ImpEx.

[andyfletcher]

Not with impex but you could with a small amount of Groovy scripting

 flexibleSearchService.search(/select {pk} from {currency} where {isocode}<>'USD'/).result.each {
   modelService.remove it
 }

Just make sure you've set the ROLLBACK slider to COMMIT