enhanced query execution: update column with NULL

[Knight]

Hi,
Getting following error on updating NULL value using Enhanced query execution job.

Query:

select concat(Name,'-Removed',' ','on-',CURRENT_TIMESTAMP) AS accounts__name,'SUSPENDED FROM IMPORT SERVICE' as accounts__status,NULL as accounts__REFERENCED_ACCOUNTKEY, accountkey as accounts__primarykey from accounts where endpointkey=3 and STATUS in ('Manually Suspended','2','Inactive') and NAME not like '%Removed on%';

Error:

Error while updating table: ACCOUNTS : Incorrect integer value: '' for column 'REFERENCED_ACCOUNTKEY' at row 1

[Dhruv_S]

Hi @Knight 

I tried with a sample query to test, and it was able to update null with similar syntax.

SELECT NULL AS ENDPOINTS__CUSTOMPROPERTY2 , ep.endpointkey as ENDPOINTS__PRIMARYKEY FROM ENDPOINTS ep ep.endpointkey=1;

Can you please check and confirm if it is giving expected result in Preview before running the job.

Regards,

Dhruv Sharma

[CR]

 Hi @Knight ,

REFERENCED_ACCOUNTKEY is big integer it wont be allow null update, try without refrencekey it will work.

[jbirkmeier]

Hi Raghu and Dhruv,

Is it possible to force null into REFERENCED_ACCOUNTKEY if needed?  Schema in Data Analyzer says that it accepts a null value.  Thanks!

[rushikeshvartak]

SELECT Concat(NAME, '-Removed', ' ', 'on-', CURRENT_TIMESTAMP) AS accounts__name
       ,
       'SUSPENDED FROM IMPORT SERVICE'                         AS
       accounts__status,
       'NULL'                                                    AS
       accounts__REFERENCED_ACCOUNTKEY,
       accountkey                                              AS
       accounts__primarykey
FROM   accounts
WHERE  endpointkey = 3
       AND status IN ( 'Manually Suspended', '2', 'Inactive' )
       AND NAME NOT LIKE '%Removed on%';