Hi,
I want to do bulk update of records in one table with value from another, as data is huge(in millions) , I'm using Advance Sql tool, but getting below error while testing(inside tool), any idea what is wrong in query?
Query :
update {PricebookRecord} SET {PricebookRecord}.[CoreAccountId] = {UltAcctParent}.[Id] FROM {PricebookRecord} Inner join {UltAcctParent} ON {PricebookRecord}.[CoreAccountName] = {UltAcctParent}.[Name] WHERE {PricebookRecord}.[CoreAccountId] = NULL ;
Error :
I have tried 4, 5 different way in query but all giving same error.
Sanjay
Hi Sanjay,
Have you tried IS NULL instead of = NULL?
Also in OutSystems if the column is an integer or long integer the default value is zero and not null, so your condition should be "= 0", if that column is a text you should use "= ' '", in oracle you need to compare with a space.
Hope this helps.
Hi Nuno,
I haven't tried with 'Is Null' but I have tried with comparing 0 also with OR condition, that didn't work either. Let me check with 'Is Null'.
Thanks.
no change with 'Is Null' also.
Have you tried to remove the ";" at the end? In MSSQL there's no issue, but maybe the way OutSystems generates the SQL for Oracle is different.
Another thing that you can validate is get the executed SQL and test directly in the database maybe this way you can understand better where's the error.
Hi Sanjay ,have you tried this way out of 5 ways of yours:-
update {PricebookRecord} SET {PricebookRecord}.[CoreAccountId] = (select {UltAcctParent}.[Id] FROM {UltAcctParent} WHERE {PricebookRecord}.[CoreAccountName] = {UltAcctParent}.[Name] ) WHERE {PricebookRecord}.[CoreAccountId] = NULL ;
also with Alias -
update PR SET PR.[CoreAccountId] = {UltAcctParent}.[Id] FROM {PricebookRecord} PR Inner join {UltAcctParent} ON PR.[CoreAccountName] = {UltAcctParent}.[Name] WHERE PR.[CoreAccountId] = NULL ;
Hi Pratap,
I tried with both of your query and got same error:
Thanks for suggestion, BTW!
Query similiar like provide to you ........here target table aslo contains in millions dataSo i suggest you provide oml file to get better understanding
well this is the same query I have written. Strange why it is not working in my app. Unfortunately I can't give OML. It looks like bug in Outsystems. Will have to do more research on this. Thanks for your time!