Impex - How to update items which have a specific attribute with empty / null value?

Question

I want to update promotions which have no productFixedUnitPrice value (productFixedUnitPrice is a collection). I've tried the following Impex script, but I've got an error.

UPDATE CustomProductFixedPricePromotion[batchmode = true]; productFixedUnitPrice[unique = true]; priority;
;; 9999 ;

The error message:

17.12.2020 13:17:58: ERROR: line 2 at main script: Flexiblesearch error: missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )
17.12.2020 13:17:58: ERROR: line 2 at main script: missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )
17.12.2020 13:17:58: ERROR: line 2 at main script: Exception 'missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )' in handling exception: missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )
17.12.2020 13:17:58: ERROR: line 2 at main script: Exception 'missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )' in handling exception: missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )
17.12.2020 13:17:58: ERROR: line 2 at main script: translated query was: SELECT  item_t0.PK  FROM promotion item_t0 WHERE ( item_t0.p_productfixedunitprice  = BINARY  ?) AND (item_t0.TypePkString=? )
17.12.2020 13:17:58: ERROR: line 2 at main script: translated query was: SELECT  item_t0.PK  FROM promotion item_t0 WHERE ( item_t0.p_productfixedunitprice  = BINARY  ?) AND (item_t0.TypePkString=? )
17.12.2020 13:17:58: ERROR: line 2 at main script: query was 'SELECT {pk} FROM {CustomProductFixedPricePromotion} WHERE {productFixedUnitPrice} = BINARY  ?productFixedUnitPrice1 '
17.12.2020 13:17:58: ERROR: line 2 at main script: query was 'SELECT {pk} FROM {CustomProductFixedPricePromotion} WHERE {productFixedUnitPrice} = BINARY  ?productFixedUnitPrice1 '
17.12.2020 13:17:58: ERROR: line 2 at main script: Flexiblesearch error: missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )
17.12.2020 13:17:58: ERROR: line 2 at main script: missing values for [productFixedUnitPrice1] ( values provided {productfixedunitprice1=[], 11=8796119367762} )

Instead of empty column, I've also tried null, <null>, <ignore> values, but I had no luck with that either:

17.12.2020 13:23:57: ERROR: line 2 at main script: Exception 'pk has wrong format: '<null>':For input string: "<null>"' in handling exception: pk has wrong format: '<null>':For input string: "<null>"
17.12.2020 13:23:57: ERROR: line 2 at main script: Exception 'pk has wrong format: '<null>':For input string: "<null>"' in handling exception: pk has wrong format: '<null>':For input string: "<null>"
17.12.2020 13:23:57: ERROR: line 2 at main script: pk has wrong format: '<null>':For input string: "<null>"
17.12.2020 13:23:57: ERROR: line 2 at main script: pk has wrong format: '<null>':For input string: "<null>"

The interesting part is that we have multiple Hybris enviroments, and in one of them this exact script was executed without any problems, but not in others. We are using Hybris 5.7.

Do you have any idea what is the problem here?

Answer 1

Im not sure if the way you are doing this can work, but here is a sure way to solve your issue :

 UPDATE GenericItem[processor=de.hybris.platform.commerceservices.impex.impl.ConfigPropertyImportProcessor];pk[unique=true]
 UPDATE CustomProductFixedPricePromotion[batchmode = true]; productFixedUnitPrice[unique = true]; priority;[default=9999];
 #% impex.initDatabase("$config-db.url", "$config-db.username","$config-db.password","$config-db.driver");
 "#%
 impex.includeSQLData( 
 ""Put your generated sql here which you can get it from sql tab after running your flexible search.""
 );
 "

change $config-db.. with you actual database configs, and add the SQL query generated from a flexible seach which gives you as result the items you want to change.

For more info : https://answers.sap.com/questions/12767995/flexible-search-in-impex-import.html