Hi,
I'm trying to push an UPDATE value to a specific field in my dataset using Data distiller. Something like
update my_dataset
set _tenant.customfield.occupation = 'updated_occupation'
where id = 'VADER00000000000000000000000000004200'
;
This returns me this: ErrorCode: 42601 queryId: <error_id> Syntax error encountered. Reason: [line 1:8: missing 'MODEL' at 'my_dataset'].
I search the community for answer and found this https://experienceleaguecommunities.adobe.com/t5/adobe-experience-platform-data/update-query-in-aep-....
The only thing I understand from this is that the UPDATE method doesn't work in data distiller and we should use INSERT INTO as a "fix" instead. Is that right?
Can someone explain to me what the error missing 'MODEL' means? Is there documentation around this?
Thanks
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
Hello @filouterrible ,
As I understand, the "UPDATE" syntax is not supported by Adobe Query Service.
The data stored in datasets is kept in Azure Data Lake, which is not a relational database. Therefore, SQL queries cannot directly interact with it in the same way they would with a typical SQL database. Instead, the queries are passed through the Adobe Query Service, which translates your SQL query into a form that the data lake layer can understand.
If the Query Service is not designed to convert an UPDATE query into a compatible format for the downstream service, it will not work, and that's the case here.
If you need to update records, I suggest using the Data Lifecycle functionality. This would involve deleting the existing records and then ingesting the updated records into the dataset again.
Hope this helps!
Kind regards,
Parvesh
Views
Replies
Total Likes
Hello @filouterrible ,
Did you try to enable the "UPSERT" on your datasets?
Create Derived Datasets with SQL | Adobe Experience Platform
Kr,
Parveh
Views
Replies
Total Likes
Views
Replies
Total Likes
Hello @filouterrible ,
As I understand, the "UPDATE" syntax is not supported by Adobe Query Service.
The data stored in datasets is kept in Azure Data Lake, which is not a relational database. Therefore, SQL queries cannot directly interact with it in the same way they would with a typical SQL database. Instead, the queries are passed through the Adobe Query Service, which translates your SQL query into a form that the data lake layer can understand.
If the Query Service is not designed to convert an UPDATE query into a compatible format for the downstream service, it will not work, and that's the case here.
If you need to update records, I suggest using the Data Lifecycle functionality. This would involve deleting the existing records and then ingesting the updated records into the dataset again.
Hope this helps!
Kind regards,
Parvesh
Views
Replies
Total Likes